Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i am using pivot table
Dimension :
Product
Measure: 1 :
service :
=If(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000000 > 1,
Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000000),'$ ####B'),
If(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000>1,
Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000000),'$ ####M'),
Num(round(sum({<SERVICE_TYPE={'Service'}>}Amount)/1000),'$ ####K')))
Measure2:
HW:
=If(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000000 > 1,
Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000000),'$ ####B'),
If(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000>1,
Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000000),'$ ####M'),
Num(round(sum({<SERVICE_TYPE={'Hardware'}>}Amount)/1000),'$ ####K')))
Measure 3: this is total sum(Measure1)+sum(Measure2) or column1 +column2
=If(sum(USD_Amount)/1000000000 > 1,
Num(round(sum(Amount)/1000000000),'$ ####B'),
If(sum(Amount)/1000000>1,
Num(round(sum(Amount)/1000000),'$ ####M'),
Num(round(sum(Amount)/1000),'$ ####K')))
My issue is Measure3 Total is not giving the correct result you can refer the screen shot. what is the issue? can anyone help me on this?
Thanks
Sony
Hi,
Try like this.
SET vScaleNumber=if($1>1000000000, num($1/1000000000,'#,##0.000B'),if($1>1000000, num($1/1000000,'#,##0.000M'),if($1>1000, num($1/1000,'#,##0.000K'),num($1,'#,##0'))));
SET ServiceAndHardware =sum({<SERVICE_TYPE={'Service','Hardware'}>}Total_Opportunity_Value_USD_Amount);
Expression 1: Service
$(vScaleNumber(sum({<SERVICE_TYPE={'Service'}>}Total_Opportunity_Value_USD_Amount)))
Expression 2: Hardware
$(vScaleNumber(sum({<SERVICE_TYPE={'Hardware'}>}Total_Opportunity_Value_USD_Amount)))
Expression 3 : Total
if($(ServiceAndHardware)>1000000000, num($(ServiceAndHardware)/1000000000,'#,##0.000B'),if($(ServiceAndHardware)>1000000, num($(ServiceAndHardware)/1000000,'#,##0.000M'),if($(ServiceAndHardware)>1000, num($(ServiceAndHardware)/1000,'#,##0.000K'),num($(ServiceAndHardware),'#,##0'))))
did you try using SERVICE_TYPE as horizontal dimension instead of creating separate expressions each reducing SERVICE_TYPE to a single value with set expressions?
The total then could be calculated with the standard partial sum option.
hope this helps
Marco
i want to show 4 tables to user s --- all four tables giving me wrong totals
1 is By Quarter
2 is by Region
3 Opp_type
4 is By SS
Not by servicetype
if i remove the if condition and that num formating all
its giving me correct sum and correct total.. May be issues with Number formatting how to change to tthe correct expression?
please post a sample application to demonstrate
Hi marcowedel
PFA sample
How is your expected result different from the results your sample application delivers?
the same number should come that is original data only i exported to excel required columns and prepared the sample.
total should give me the correct
Hi,
Try like this.
SET vScaleNumber=if($1>1000000000, num($1/1000000000,'#,##0.000B'),if($1>1000000, num($1/1000000,'#,##0.000M'),if($1>1000, num($1/1000,'#,##0.000K'),num($1,'#,##0'))));
SET ServiceAndHardware =sum({<SERVICE_TYPE={'Service','Hardware'}>}Total_Opportunity_Value_USD_Amount);
Expression 1: Service
$(vScaleNumber(sum({<SERVICE_TYPE={'Service'}>}Total_Opportunity_Value_USD_Amount)))
Expression 2: Hardware
$(vScaleNumber(sum({<SERVICE_TYPE={'Hardware'}>}Total_Opportunity_Value_USD_Amount)))
Expression 3 : Total
if($(ServiceAndHardware)>1000000000, num($(ServiceAndHardware)/1000000000,'#,##0.000B'),if($(ServiceAndHardware)>1000000, num($(ServiceAndHardware)/1000000,'#,##0.000M'),if($(ServiceAndHardware)>1000, num($(ServiceAndHardware)/1000,'#,##0.000K'),num($(ServiceAndHardware),'#,##0'))))
thanks arjun... Not yet tested. i will test and update you