How to sum a measure in a table?

[Anonymous]

Hi all,

Sorry if this has been asked before, but I am really struggling here. My boss has tasked me with replicating an excel report using Power BI, and I am nearly there but for this one issue.

I am trying to calculate a very bespoke sales uplift, which in excel is relatively simple as you point the formula at the cells you need. However in Power BI it has required numerous DAX measures to get to this point, but that's ok. So, so far I have:

1. YoY SHARE % CHANGE 2019 vs 2018 CALL FILE (taking the % share from one year and subtracting from another year for all stores that are on the call file)
2. YoY SHARE % CHANGE 2019 vs 2018 NOT CALL FILE (taking the % share from one year and subtracting from another year for all stores that are not on the call file)
3. YoY SHARE % CHANGE 2020 vs 2018 CALL FILE (as above)
4. YoY SHARE % CHANGE 2020 vs 2018 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 (sum of filtered 2018 sales data * measure 5)
8. INCR SALES 2020 (sum of filtered 2018 sales data * measure 6)

As you can see, there are several measures required to get to this point, and if I plot as a table it looks ok and the values match my excel report. However the Total rows do not add up:

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

Any ideas? Sorry for the long post...

Reagrds,

Rick

[v-zhenbw-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We create a sample, a date table and a fact table, they are based on one-to-many relationship.

And we create a measure that you can refer.

sum average = 
var x = [averange]
Return 
SUMX(VALUES('date'[Month]),x)

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

Please don't contain any Confidential Information or Real data in your reply.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[MFelix]

Hi @Anonymous ,

You need to use an aggregator measure (the ones that end with an X) in your case SUMX.

Redo your measure to something similar to this:

Increase in Sales = 

var increase_in_sales = [Increase in Sales]
Return 
IF(HASONEVALUE(Calendar[Month]);increase_in_sales; SUMX(Calendar; increase_in_sales))

This is checking if the month is only a single value if yes then return the measure otherwise makes the SUMX.

Replace the value in the variable [Increase in Sales] by the code of your measure (if you don't want to create a new measure otherwise use your measure in var. Also I assume you have a calendar table named calendar just replace by the table name of your date column.