Problem in automatical sum of pivot table

[Vince_CH]

 Hello Community,

I make a pivot tabel as below to list the YTD budget in conditional expressions as following, assignment of different yearly numbers as per different conditions. the result is, that the figures in pivot table cannot be summed automatically.  how shall i resovle this issue? or any other alternative ways? thanks

 

Num(
(If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)*

If(Order_Material_Pricing_Group_Text='spare parts' and Subsidiary='Subsidiary' and [Order Type]='Y410','2400',
If(Order_Material_Pricing_Group_Text='spare parts' and [Order Type]='Y410','14550',
If(Order_Material_Pricing_Group_Text='grinding media' and Subsidiary='Subsidiary' and [Order Type]='Y410','0',
If(Order_Material_Pricing_Group_Text='grinding media' and [Order Type]='Y410','2000',
If(Order_Material_Pricing_Group_Text='service' and Subsidiary='Subsidiary' and [Order Type]='Y400','0',
If(Order_Material_Pricing_Group_Text='service' and [Order Type]='Y400','650',
If(Order_Material_Pricing_Group_Text='repair works' and [Order Type]='Y400','0'))))))),

'¥#,##0K;($#,##0.00)')

[Anil_Babu_Samineni]

You may try this way?

Num(
Sum(Aggr((If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)*

If(Order_Material_Pricing_Group_Text='spare parts' and Subsidiary='Subsidiary' and [Order Type]='Y410','2400',
If(Order_Material_Pricing_Group_Text='spare parts' and [Order Type]='Y410','14550',
If(Order_Material_Pricing_Group_Text='grinding media' and Subsidiary='Subsidiary' and [Order Type]='Y410','0',
If(Order_Material_Pricing_Group_Text='grinding media' and [Order Type]='Y410','2000',
If(Order_Material_Pricing_Group_Text='service' and Subsidiary='Subsidiary' and [Order Type]='Y400','0',
If(Order_Material_Pricing_Group_Text='service' and [Order Type]='Y400','650',
If(Order_Material_Pricing_Group_Text='repair works' and [Order Type]='Y400','0'))))))), Category, Region, OrderType)), '¥#,##0K;($#,##0.00)')

[Vince_CH]

@Loverisfail, thanks for reply.

but the dimensions mentioned are also with conditional expressions like following, i cannot use Aggr() simply. any other suggestions?

Region:
=If(Subsidiary='Subsidiary','IC','China/Asia')

Category:
=If(Order_Material_Pricing_Group_Text='spare parts' or Order_Material_Pricing_Group_Text='grinding media' or
Order_Material_Pricing_Group_Text='service' or Order_Material_Pricing_Group_Text='repair works',Order_Material_Pricing_Group_Text)
//with empty value omitted

Order_Type
=If([Order Type]='Y400' or [Order Type]='Y410',[Order Type])

[Anil_Babu_Samineni]

You can, Because you are using single dimension for condition as Calc. Dim. So, You may use

Num(
Sum(Aggr((If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)*

If(Order_Material_Pricing_Group_Text='spare parts' and Subsidiary='Subsidiary' and [Order Type]='Y410','2400',
If(Order_Material_Pricing_Group_Text='spare parts' and [Order Type]='Y410','14550',
If(Order_Material_Pricing_Group_Text='grinding media' and Subsidiary='Subsidiary' and [Order Type]='Y410','0',
If(Order_Material_Pricing_Group_Text='grinding media' and [Order Type]='Y410','2000',
If(Order_Material_Pricing_Group_Text='service' and Subsidiary='Subsidiary' and [Order Type]='Y400','0',
If(Order_Material_Pricing_Group_Text='service' and [Order Type]='Y400','650',
If(Order_Material_Pricing_Group_Text='repair works' and [Order Type]='Y400','0'))))))), Order_Material_Pricing_Group_Text, Subsidiary, [Order Type])), '¥#,##0K;($#,##0.00)')

[Vince_CH]

@Anil_Babu_Samineni, thanks , it works.

however, I found some discrepancy between the results from this pivot table and my KPI results, as photo illustrated. 

in the KPI, the expression of budget is like following.

While the part of expression in pivot table for the mentioned budget is red fonts indicated which extrated from the big agg() expressions.  so what is the reason of the gap here?

//KPI budget expression:

Num(
(If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)*2400,
'¥#,##0K;($#,##0.00)')

//Part expressions from the agg() expressions related with this budget

If(Order_Material_Pricing_Group_Text='spare parts' and Subsidiary='Subsidiary' and [Order Type]='Y410','2400',

[Anil_Babu_Samineni]

Difficult to say this part now? BTW, If else condition not the idea to use in KPI for Accuracy. Could Sum(Aggr(..., Field,..)) must be added. What you have written in pivot table and KPI to show the same number?

[Vince_CH]

Yes, exactly.
In Kpi, just with the percentage of so far days in the years mulitple the yearly budget numbers, which should be correct as the budget, as expressions mentioned in above post.

In pivot table expressions of Aggr() as you suggested, there are a number of budget numbers assigned as per different condition, but the target is the same category for sure.

Due to the discrepancy, i am confused which one shall I trust? how can it come?

[Anil_Babu_Samineni]

When you say discrepancy, That we can't validate until unless check in Data level. But, For sure - The measure Sum(Aggr(..., Field,..)) always returning perfect value {Only decimal will concern} in my case.

[Vince_CH]

but how to use the same Sum(Aggr(..., Field,..)) for a fixed budget number like 2400 in KPI??

[Anil_Babu_Samineni]

Below condition works, When ever you select the field values.

If((Order_Material_Pricing_Group_Text='spare parts' and Subsidiary='Subsidiary') and [Order Type]='Y410','2400')

If you want default as 2400 for all filter by restricted, Try this?

Sum({<Order_Material_Pricing_Group_Text = {'spare parts'}, Subsidiary = {'Subsidiary'}, [Order Type] = {'Y410'}>} 2400)