Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I've been working on a Finance PVM (Price Volume Mix) Analysis in PowerBI and have been stuck with a SUMX DAX measure that doesn't return the intended value.
I have tried multiple way-outs and even brought the semantic model in a flat file to simplify things but still no resolution.
The model has Region and product hierarchies against which we have Sales, Units, and SGP for various scenarios. The visual has parameters that allow users to select scenarios selections (Actual/Forecast/Plan) which are then linked to measures.
And then there are a few calculated measures to calculate contributions of Sales and SGP of each SKU to total Sales and there are deltas between scenarios to calculate Mix.
The product of Sales Contribution Delta and SGP contribution Delta gives a Mix Factor, which is then multiplied by Total Sales to give Mix $.
This Mix$ is the right value when I see it at the lowest level of the product hierarchy; however, as soon as I use SUMX to achieve Mix$ for level 2 of the product hierarchy it does not sum and gives methe wrong value or zero.
I can share some files if you could help with this.
I've been stuck with this for far too long.
Any help would be appreciated.
@kunwar2 , Not very clear. But make sure you measure is like
Sumx(Summarize(Fact, Dim[Col], Dim2[Col]), [Measure1]*[Measure2])
In it from two fact and across different dimesions
Sumx(Values(Dim[Col]), calculate(Sumx(Values(Dim2[Col]), [Measure1]*[Measure2]) ) )
You can use isinscope to change calculation for different level
example
How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
85 | |
66 | |
52 | |
47 |
User | Count |
---|---|
215 | |
90 | |
83 | |
66 | |
58 |