Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a matrix using a measure with the DAX expression 'Expense / Sales'.
So each row represents a % of total sales. see below:
Currently my subtotal for the 3 year average would be 30.25%; however, I want my row-level sub-total to be the inverse of what it currently is. So the individual rows are correct, but the subtotal needs to be (1 - 30.25%) = 69.75%
How can I modify by DAX expression so it calculates the sub-total slightly different than the rest of the table.
Thanks
Solved! Go to Solution.
Hi @Fraze
You can use HASONEVALUE to modify the calculation logic of subtotal.
Try measure as:
IF(
HASONEVALUE('Table'[Column]),
measure,
new measure //calculation logic of '69.75%'
)
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Fraze
You can use HASONEVALUE to modify the calculation logic of subtotal.
Try measure as:
IF(
HASONEVALUE('Table'[Column]),
measure,
new measure //calculation logic of '69.75%'
)
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi Fraze,
You could use the ISFILTERED() function to determine if the year is being filtered, and calculate the measure accordingly. My example assumes your data is structured as below:
With the DAX function:
COGS% =
AVERAGEX(
'Table',
IF(
ISFILTERED('Table'[Year]),
DIVIDE('Table'[Expenses],'Table'[Sales]),
1 - DIVIDE('Table'[Expenses],'Table'[Sales])
)
)
It returns the following result:
If you have other slicers or filters affecting the Year field, then you may need to use a logical rule such as COUNTROWS(DISTINCT()) or similar to calculate the 3 year scenario.
Edit: Tweaked the function to use the safe DIVIDE function rather than slashes.
Thanks for the information.
Unfortunatly, my date does have some filters in the background. In addition to what you already mentioned, Any further insight on how to best deal with that.
Thanks
Hi Fraze,
Just following up to see if this solved your issue?
You could use DISTINCTCOUNT('Table'[Year]) > 1 instead of the ISFILTERED('Table'[Year]) statement in the above function and that should still work assuming you want any non-individual subtotals to show the inverse side.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |