I am trying to come up with a Measure that calculates the Max value of Diff% in the following visuals based on the level the data is displayed.
On below screen, the leftmost visual is my raw data in Table, basically Category, Product, Actual and Target columns.
The middle one is the things when viewed from Categories point of view and the last visual is on Category and Product.
I have created measures MAct%, MTar%, MDiff% that are working fine on the last two visuals fine.
I have tried several ways but couldn't get to it. Please help. Here is the Power BI file for easy testing. https://www.dropbox.com/s/q08c64vb8ijkop0/TestProblem.pbix?dl=0
Solved! Go to Solution.
This is your code:
MDiff% = MAXX(VALUES('Table'[Product]);[MTar%]-[MAct%])
As seen here:
Pls mark as solution if this works for you. Thumbs up for the effort are appreciated.
Kind regards, Steve.
This one was pretty nice. I was looking for that filter that can tell me if my visual was on a certail details level. Thank you Allison.
I added bit of real world complexity to the problem and the new book is over here.
There is a Company dimension as well to the data and everything needs to be seen from Companies perspective. The idea is the Diff% if above a certain threshold, it should be higlited in default view. Which I could do using the Diff%. But also allow users to filter companies in following ways:
1. See all of them and exceptions highlited on particular rows, that are higher than threshold.
2. See only Companies with exceptions, still exceptions highlited,
3. See companies without any exceptions.
MDiff%-Threshold = [MDiff%] - SELECTEDVALUE('ThresholdDiff%'[ThresholdDiff%])
MDiff%-Threshold measure allows to do the highligting based on the selection done to the table ThresholdDiff% one column table, with values ranging from 1 to 10.
I asked for calculation of MMaxDiff% so the whole Company can be marked as having an exception or not.
So inspired from Allison's code my MMaxDiff% is:
MMaxDiff% = IF(HASONEVALUE('Table'[Pro]), CALCULATE(maxx(SUMMARIZE('Table', [Comp],[Cat],[Pro]), [MDiff%]), ALL('Table'[Cat]), ALL('Table'[Pro])), CALCULATE(maxx(SUMMARIZE('Table', [Comp],[Cat]), [MDiff%]), ALL('Table'[Cat])))
Before we allow users to filter it, it still needs to get checked against the ThresholdDiff%:
ShowCompaniesWithException = if(([MMaxDiff%] - SELECTEDVALUE('ThresholdDiff%'[ThresholdDiff%]))>0, 1, 0)
So, finally ShowCompaniesWithException allows user to filter in one of the 3 ways described earlier. But one thing, is this filter is based of a Measure, so it can not be placed in page filters or all page filters, and only on visual filters.
Following image is what I am trying to explain above. As you can see, Company 2 is not shown on both the visuals as they are only showing Companies that have Exceptions.
Is there a better way to do all this?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.