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.
Hi
I have a slicer for Company and I have to calculate the contribution of the below assets to the AUM of the selected companies.
For instance if on the slicer i select CH and IE then I will have to divide the AUM of each asset of CH and IE by the sum of all assets within comapnies CH and IE.
If on the slicer i select CH and IT then I will have to divide the AUM of each asset of CH and IT by the sum of all assets within comapnies CH and IT.
Basically the denominator will have to adjust dinamically to what is selected on the slicer. How can I implement this?
Company | Asset | AUM | |
CH | CH01 | 250,00 € | |
CH | CH02 | 100,00 € | |
IE | IE01 | 100,00 € | |
IE | IE02 | 500,00 € | |
IT | IT01 | 400,00 € | |
IT | IT02 | 300,00 € |
I tried the below but it does not work
Solved! Go to Solution.
Try this:
Contribution =
VAR vNumerator = [Total AUM]
VAR vDenominator =
CALCULATE ( [Total AUM], ALLSELECTED () )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Proud to be a Super User!
Try these measures:
Total AUM = SUM ( TEST[AUM] )
Contribution =
VAR vNumerator = [Total AUM]
VAR vDenominator =
CALCULATE ( [Total AUM], ALLSELECTED ( TEST ), VALUES ( TEST[Company] ) )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Proud to be a Super User!
Many Thanks @DataInsights .
For asset CH01 I would expect controbution to be 0.263 (which should be the result of 250/950)
For asset CH02 I would expect controbution to be 0.105 (which should be the result of 100/950)
For asset IE01 I would expect controbution to be 0.105 (which should be the result of 100/950)
For asset IE02 I would expect controbution to be 0.526 (which should be the result of 500/950)
Try this:
Contribution =
VAR vNumerator = [Total AUM]
VAR vDenominator =
CALCULATE ( [Total AUM], ALLSELECTED () )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Proud to be a Super User!
Thanks @DataInsights , would you know how to implement this using columns? I would need the contribution as a column rather than a measure
A calculated column doesn't recognize slicer selections, so you would need to use a measure.
Proud to be a Super User!
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |