How to sum a measure in a table?

[Anonymous]

Hello everyone

I'm sorry if this has been asked before, but I'm really fighting here. My boss has commissioned me to replicate an Excel report with Power BI, and I'm almost there, but for this one problem.

I'm trying to calculate a very custom sales increase, which in Excel is relatively simple as you point the formula to the cells you need. However, Power BI has required numerous DAX measures to get to this point, but that's fine. So, so far I have:

1. YoY SHARE % CHANGE 2019 vs 2018 CALL FILE (taking the % of the one-year quota and subtracting from another year for all stores that are in the call file)
2. YoY SHARE % CHANGE 2019 vs 2018 DO NOT CALL FILE(take the one-year odds percentage and subtract from another year for all stores that are not in the call file)
3. YoY SHARE % CHANGE 2020 vs 2018 CALL FILE (as above)
4. YoY SHARE % CHANGE 2020 vs 2018 DO NOT CALL FILE (as above)
5. SHARE % DIFF 2019 vs 2018 CALL FILE VS NOT CALL FILE (measure 1 - measure 2)
6. SHARE % DIFF 2020 vs 2018 CALL FILE VS NOT CALL FILE (measure 3 - measure 4)
7. INCR SALES 2019 (2018 filtered sales dame sum * measure 5)
8. INCR SALES 2020 (2018 filtered sales dame sum * measure 6)

As you can see, there are several measures needed to get to this point, and if I plan as a table looks good and the values match my Excel report. However, the Total rows do not add up:

My	INCR 2019 VENTAS	RETAILER 1 INCR SALES 2019	RETAILER 2 INCR SALES 2019	RETAILER 3 INCR SALES 2019	RETAILER 4 INCR SALES 2019	INCR SALES 2020	RETAILER 1 INCR SALES 2020	RETAILER 2 INCR SALES 2020	RETAILER 3 INCR SALES 2020	RETAILER 4 INCR SALES 2020
January	44.304 euros	1.611 euros	-11.184 euros	26.107 euros	27,771 euros	238.087 euros	41.341 euros	15.644 euros	18,788 euros	£162,314
February	136,427 euros	-1.418 euros	-6.561 euros	32.742 euros	111.664 euros	272,442 euros	22.908 euros	8.453 euros	48.007 euros	193.073 euros
March	147,459 euros	7.027 euros	-3,599 euros	78.056 euros	65.976 euros	129,178 euros	54.368 euros	2.871 euros	£30,423	41.515 euros
April	211.106 euros	1.719 euros	-15.873 euros	97.766 euros	127,495 euros
Can	248,852 euros	£18,191	£24,980	60.964 euros	144.716 euros
June	365.241 euros	25.249 euros	16.516 euros	128,764 euros	£194,713
July	271.421 euros	-27.066 euros	79.009 euros	27,485 euros	191.993 euros
August	224,874 euros	-1.282 euros	58.197 euros	65,183 euros	102.777 euros
September	113,407 euros	-38.918 euros	75.814 euros	39.257 euros	37.254 euros
October	78,997 euros	-7.884 euros	47.042 euros	6.998 euros	32.842 euros
November	£179,791	-199 euros	40.436 euros	13.981 euros	125,573 euros
December	155.035 euros	£24,700	15.484 euros	-19.527 euros	134,377 euros
Total	2.208.968 euros	6.501 euros	298.042 euros	566,703 euros	1.337,723 euros	2.766,599 euros	£551,740	214,851 euros	242.857 euros	1.757,151 euros
HOW TOTAL SHOULD IT BE	2.176,914 euros	£1,730	320.261 euros	557,776 euros	1.297,151 euros	639,707 euros	118,617 euros	26,968 euros	97.218 euros	396,902 euros

I understand that the DAX formula is applying the same calculations to the TOTAL row, but I just need to sum what's above! I've seen similar threads where people have had the same problem, but their data is always based on hard-coded data rather than fields.

Any ideas? I'm sorry about the long post...

Reagrds,

Rick

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[AllisonKennedy]

Hi Rick

Sorry if I missed it but can you share the formulas for the measures you have already calculated, as you say this is a common problem with totals seeming 'wrong' in measure calculation, so if you can share your DAX formulas for the measures we can help you understand why they are 'wrong' and how you can fix them. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Hi Allison,

Thanks for getting back to me! The formulas I've provided are as follows:

1. 

DRINK NOW - RETAILER1 YoY SHARE % CHANGE 2019 vs 2018 CALL FILE =
CALCULATE( sum( Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]),
Data[Callfile] = "Callfile" ,
Data[Year] = 2019 ,
Data[RETAILER] = "RETAILER1")
-
CALCULATE( sum( Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]), Data[Callfile] = "Callfile" , Data[Year] = 2018 , Data[RETAILER] = "RETAILER1")

2.

DRINK NOW - RETAILER1 YoY SHARE % CHANGE 2019 vs 2018 NOT CALL FILE =
CALCULATE( sum( Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]), Data[Callfile] = "Not Callfile" , Data[Year] = 2019 , Data[RETAILER] = "RETAILER1")
-
CALCULATE( sum( Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]), Data[Callfile] = "Not Callfile" , Data[Year] = 2018 , Data[RETAILER] = "RETAILER1")

3. 

DRINK NOW - RETAILER1 YoY SHARE % CHANGE 2020 vs 2018 CALL FILE =
if (
ISBLANK(
CALCULATE(
sum(Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]),
Data[Callfile] = "Callfile" ,
Data[Year] = 2020 ,
Data[RETAILER] = "RETAILER1") ) ,
BLANK() ,
CALCULATE( sum( Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]), Data[Callfile] = "Callfile" , Data[Year] = 2020 , Data[RETAILER] = "RETAILER1")
-
CALCULATE( sum( Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]), Data[Callfile] = "Callfile" , Data[Year] = 2018 , Data[RETAILER] = "RETAILER1") )

4.

DRINK NOW - RETAILER1 YoY SHARE % CHANGE 2020 vs 2018 NOT CALL FILE =
if (
ISBLANK(
CALCULATE(
sum(Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]),
Data[Callfile] = "Not Callfile" ,
Data[Year] = 2020 ,
Data[RETAILER] = "RETAILER1") ) ,
BLANK() ,
CALCULATE( sum( Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]), Data[Callfile] = "Not Callfile" , Data[Year] = 2020 , Data[RETAILER] = "RETAILER1")
-
CALCULATE( sum( Data[DRINK NOW - CLIENT]) / sum(Data[DRINK NOW - CATEGORY]), Data[Callfile] = "Not Callfile" , Data[Year] = 2018 , Data[RETAILER] = "RETAILER1") )

5.

DRINK NOW - RETAILER1 SHARE % DIFF 2019 vs 2018 CALL FILE VS NOT CALL FILE =

[DRINK NOW - RETAILER1 YoY SHARE % CHANGE 2019 vs 2018 CALL FILE] - [DRINK NOW - RETAILER1 YoY SHARE % CHANGE 2019 vs 2018 NOT CALL FILE]

6.

DRINK NOW - RETAILER1 SHARE % DIFF 2020 vs 2018 CALL FILE VS NOT CALL FILE =

[DRINK NOW - RETAILER1 YoY SHARE % CHANGE 2020 vs 2018 CALL FILE] - [DRINK NOW - RETAILER1 YoY SHARE % CHANGE 2020 vs 2018 NOT CALL FILE]

7.

DRINK NOW - RETAILER1 INCR SALES 2019 =

CALCULATE(
sum(Data[DRINK NOW - CATEGORY]),
Data[Callfile] = "Callfile" ,
Data[Year] = 2018 ,
Data[RETAILER] = "RETAILER1" )
* [DRINK NOW - RETAILER1 SHARE % DIFF 2019 vs 2018 CALL FILE VS NOT CALL FILE]

8.

DRINK NOW - RETAILER1 INCR SALES 2020 =

CALCULATE(
sum(Data[DRINK NOW - CATEGORY]),
Data[Callfile] = "Callfile" ,
Data[Year] = 2018 ,
Data[RETAILER] = "RETAILER1" )
* [DRINK NOW - RETAILER1 SHARE % DIFF 2020 vs 2018 CALL FILE VS NOT CALL FILE]

In short, formulas 1 & 2 are used to make formula 5, which is then in turn applied with data from the original source to create formula 7.

Sorry for the long formulas, I am self-taught for the most part!

[mahoneypat · Accepted solution]

Try using this measure pattern instead.  Keep your existing measures as you'll reference them in the new ones.

NewMeasure = sumx(values(Date[Month]), [ExistingMeasure]) // or use Date[YearMonth]

This measure pattern should give you the right result in both the rows and the total.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks Pat - this worked a charm!