Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SofiArtigas
Frequent Visitor

PIVOT Table subtotals and total does not SUM correctly

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:

SofiArtigas_0-1698242058067.png

 

The  measure is the  following:

SofiArtigas_1-1698242125621.png

 Thank you




1 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

lbendlin_0-1698511828530.png

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)

 

View solution in original post

11 REPLIES 11
SofiArtigas
Frequent Visitor

Done with the access

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

lbendlin_0-1698511828530.png

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)

 

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

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

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. 

SofiArtigas_0-1699517398306.png

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




 
SofiArtigas
Frequent Visitor

Ask me  for permission.

v-rongtiep-msft
Community Support
Community Support

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.

 

 

 

 

 

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:

SofiArtigas_1-1698396914234.png

 

DataSet 

lbendlin
Super User
Super User

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...

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:

SofiArtigas_0-1698396552932.png

Dataset 

access denied

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.