Forum Discussion
Community ChampionDealing with Measure Totals
Resolver IIII tend to avoid measures that are designed with a specific visualization in mind, but indeed, in the case of non-standard totals it's inevitable. In your example, the assumption is that the years are the identifiers for the rows in the table. When an additional level, e.g. Month, is added, then the behaviour of the measure will be - different.
But this aside, the part of your formula for the total would work just as well on the detail rows when iterating over VALUES(Table[Year] instead of Table itself:
MyMeasure3 = SUMX(FILTER(VALUES(Table[Year]),[Amount]>1000),[Amount]-1000)
On a detail row, VALUES(Table[Year]) would contain only one row and the filtered table is empty when [Amount]<=1000. This means that rows for years with [Amount] lower than 1000 will have a blank value. If you do want to have 0 instead of blank, just add 0 to the result:
MyMeasure3 = SUMX(FILTER(VALUES(Table[Year]),[Amount]>1000),[Amount]-1000) + 0
Are you able to provide a visual of what this looks like when it's completed?
- jcarville
Skilled Sharer
A really useful write-up, and one I have used previously.
I have a strange issue where using HASONEFILTER or HASONEVALUE has not worked for me in calcualting the totals I would expect. See my post here, if anyone can help me: https://community.powerbi.com/t5/Desktop/Incorrect-Measure-Total/m-p/454679#M210659
- jcarville
Just an update to my cry for help above!
I was given a solution that worked for me that involved having a SUMMARIZE function wrapped within my SUMX function. It could be useful if anyone else comes upon this guide, but is still having an issue with what they expect the total to be.
http://community.powerbi.com/t5/Desktop/Incorrect-Measure-Total/m-p/454679#M210659
This made my world much easier today!
