PIVOT Table subtotals and total does not SUM correctly

[SofiArtigas]

Hi!

I  create  a pivot table where rows have 5  differente levels.  The  dataset has  two columns "P1_IM (FDR)" and  "P1 UNITS".  I created a  measure where  I divided  "P1 IM (FDR)"  by "P1 Units". The  Problem es that the  calculation on the  level  5 is for example 268,49 and 387.18 but in Level 4, when the  pivot make the  SUM the value is 347.62:

The  measure is the  following:

 Thank you

[SofiArtigas]

Done with the access

[lbendlin · Accepted solution]

You are not calculating a sum, you are calculating an average.  To be precise you are calculating the average across two/three averages (but not the average of averages).

If you instead intend to sum up the averages you need to indicate that in your measure.  This is complicated by the fact that one of your rows doesn't have data

You need to decide if you want to calculate the measure while ignoring that blank row, or refuse to calculate the measure (as I would do) or include the blank row with a zero value.

In either case you need to clearly indicate that that is the sum of averages as your business users will intuitively assume something else (ie the average of sums)

[SofiArtigas]

I'm still having  issues. I corrected  the  nulls  but still have  the difference.  Can you provide more  help? 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[SofiArtigas]

Hi

I have a list of  products table that are  categorized  in  4 levels, for example,  if the  product es  a football  ball,  levels will be ---> sports/Team Sports/Football/accessories. 
I need to  make a comparisson  between  products  sold in period  1  and  in period 2.
Step 1:  

P1_Unit_FDR_IM = DIVIDE('Tabla '[P1_IM_(FDR)],'Tabla'[Unit 1]) ---> I divide the  internal Margin for each unit  of  the product sold  in period 1. 

Step 2: 

v2Sales_Units_P1_L4 = CALCULATE(SUM('Tabla '[Unit 1]), ALLEXCEPT('Tabla','Tabla'[GPC - Level 1], 'Tabla'[GPC -Level  2],'Tabla'[GPC - Level 3],'Tabla'[GPC - Level 4],'Tabla'[Exclusión]))
Here,  what I´m doing  is to  calculate  the  total,  for example,  I sold 2 of  product  1, and  3  of  product 2,  both belongs to sports/Team Sports/Football/accessories (the  whole  level), so  I  what to have a number  of 5 not  the  quatity  of each  product  of that category.
Step 3:

v2Sales_units_Mix_P1_L4 = DIVIDE(SUM('Tabla '[Unit 1]),[v2Sales_Units_P1_L4]) 
This will give  me  the  proportion  of  each  product.
Step 4:

Sales_Units_p2_l4_@_MixP1 = SUMX('Tabla',[UNIT P2_L4]*[v2Sales_units_Mix_P1_L4])
Here I see a comparison  of sales  in period 2 of the product  in  compararisson  of the proportion  of sales  of  that  product  in period  1.
Step 5:

DiffUnits = CALCULATE(SUM('Tabla'[Unit 2])-SUM('Tabla'[Unit 1]))
Difference  of units sold in period 2 vs  period 1.
Step 6:

V2Unit_IM_to_consider = SUMX('Tabla', if (SUM('Tabla'[Unit 1<>0,  'Tabla'[P1_Unit_FDR_IM],'Tabla'[P2_Unit_FDR_IM]))

Here I'm telling,  if  I don't have a  value  in  period 1 for the  product,  take the  one  in period 2.
Step 7:
I need  to know the  impact : 

v2Impact_(Total) =

VAR SUMA = CALCULATE(SUM('Tabla'[DiffUnits])*[V2Unit_IM_to_consider],ALLEXCEPT('Tabla ','Tabla'[GPC - Level 1],'Tabla'[GPC -Level  2],'Tabla'[GPC - Level 3],'Tabla'[GPC - Level 4],'Tabla '[Exclusión],'Tabla'[Item Global Name + code]))

RETURN

SUMA
And  then  I use  this  measure to calculate the correct  number:

V2Impact (Total) =

var xx=

ADDCOLUMNS(

    summarize(

        SELECTCOLUMNS(

                CALCULATETABLE(

                'Tabla'

                ,'Tabla'[Exclusion] = "N"

            ),

            "item Code", 'Tabla'[Item Global Name + code]

        ),

        [Item code]

    ),

    "impact", [v2Impact_(Total)]

)

return

sumx(xx, [impact])

Till here we are  fine. 
Step 8: 

V2Impact (Quantity_L4) = CALCULATE(([P2xmix]-sum('Tabla'[Unit 1]))*[V2Unit_IM_to_consider],ALLEXCEPT('Tabla','Tabla'[GPC - Level 1],'Tabla'[GPC -Level  2],'Tabla '[GPC - Level 3],'Tabla'[GPC - Level 4],'Tabla'[Exclusión],'Tabla'[Item Global Name + code]))

The  same  way  on the step 7 I  used this  measure  to  calculate  the  following:

V2_Tabla_Impact (Quantity_L4) =

var xx=

ADDCOLUMNS(

    summarize(

        SELECTCOLUMNS(

                CALCULATETABLE(

                'Tabla'

                ,'Tabla'[Exclusión] = "N"

            ),

            "item Code", 'Tabla'[Item Global Name + code]

        ),

        [Item code]

    ),

    "impact", [V2Impact (Quantity_L4)]

)

return

sumx(xx, [impact])

and the  outcome is the  following:
If you can see,  on the product code and  name the values are -89.50 - 258.12 = -347.62 which is  ok .  But If I  go to the  next  level the  values are = -347.62-290896-738294.10-72374-93275-2885-22373-30679 = -1251126.
and what  we  see  on the  matrix table  es -1319099,10. So it is  not summing correctly the values  that is calculating  correctly  for  each  product. 

The sample dataset it's  on the drive I  already provided. 
Thank you

[SofiArtigas]

Ask me  for permission.

[Anonymous]

Hi @SofiArtigas ,

Please have a try.

Ratio of Sums = DIVIDE ( SUM ( Table[P1_IM (FDR)] ), SUM ( Table[P1 Units] ) )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SofiArtigas]

Hi,  thank  you for  answering. 

I  attached the  link to the data set (which is  bigger)  with the  most  important  information.  I  also attached  the calculation in excel  of  what  I'm doing. 
Once I  create the  measures  the issue is that   the subtotals  does not calculate correctly the value,  row per  row.  For  example,  in this subtotal the  value is -1.319.099,10 but  if I sum the values,  the correct  one  should  be  -1.251.126,59:

DataSet 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[SofiArtigas]

Hi,  thank  you for  answering. 

I  attached the  link to the data set (which is  bigger)  with the  most  important  information.  I  also attached  the calculation in excel  of  what  I'm doing. 
Once I  create the  measures  the issue is that   the subtotals  does not calculate correctly the value,  row per  row.  For  example,  in this subtotal the  value is -1.319.099,10 but  if I sum the values,  the correct  one  should  be  -1.251.126,59:

Dataset 

[lbendlin]

access denied