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 would like use the result of the column "R" in my report. Actually it should be: =((PD/C)/((YD*4)/(I+M))).The table is:
Subunit | YD | I | M | C | PD | 4YD | IandM | Div (4YD/IandM) | Civ (PD/C) | R (Civ/Div) |
1 | 70000 | 6409 | 75907 | 1 | 250 | 280000 | 82316 | 3.40 | 250.00 | 73.496699 |
2 | 1838625 | 571592 | 1838625 | 2 | 400 | 7354498 | 2410217 | 3.05 | 200.00 | 65.544021 |
3 | 694669 | 350000 | 700000 | 1 | 250 | 2778676 | 1050000 | 2.65 | 250.00 | 94.469461 |
4 | 1174711 | 426558 | 1174711 | 2 | 400 | 4698844 | 1601269 | 2.93 | 200.00 | 68.155853 |
5 | 3322875 | 1273950 | 3322875 | 1 | 250 | 13291501 | 4596825 | 2.89 | 250.00 | 86.461733 |
6 | 7429321 | 10987703 | 7429321 | 2 | 400 | 29717284 | 18417024 | 1.61 | 200.00 | 123.94823 |
7 | 4140593 | 1438175 | 4140593 | 1 | 250 | 16562374 | 5578769 | 2.97 | 250.00 | 84.208474 |
8 | 450000 | 598844 | 450000 | 2 | 400 | 1800000 | 1048844 | 1.72 | 200.00 | 116.53826 |
Total | 19120794 | 15653232 | 19132032 | 12 | 2600 | 76483176 | 34785264 | 2.20 | 216.67 | 98.542027 |
Please note that TOTAL is not included in the table. I put here to explain the problem.
In order to do that, I added some columns in power query editor:
- 4YD is YD*4
- IandM is I+M
- Div is 4YD/IandM or =((YD*4)//I+M))
- Civ is PD/C
- R is Civ/Div which is ((PD/C)/((YD*4)/(I+M)))
I used the following measure to show the result based on selection:
Measure = CALCULATE(DIVIDE(SUM(Table[Civ]),sum(Table[Div]))
I see the result for each subunit is correct. However, when I show the result for total, it doesn't give the correct result. I have more than one unit. It is possible for a user to select more than one unit. Therefore I can not use the row Total. Could you help me how to correct it? In my formula, I get the avarega as Div for total, but it should not do it in that way.
All the best
Solved! Go to Solution.
Hi @IF ,
Civ and Div are created by other columns, you could use the following DAX:
Measure 2 =
VAR a =
DIVIDE ( SUM ( 'Table'[PD] ), SUM ( 'Table'[C] ) )
VAR b =
DIVIDE ( 4 * SUM ( 'Table'[YD] ), SUM ( 'Table'[I] ) + SUM ( 'Table'[M] ) )
RETURN
a / b
Here is my test result.
Hi @IF ,
Civ and Div are created by other columns, you could use the following DAX:
Measure 2 =
VAR a =
DIVIDE ( SUM ( 'Table'[PD] ), SUM ( 'Table'[C] ) )
VAR b =
DIVIDE ( 4 * SUM ( 'Table'[YD] ), SUM ( 'Table'[I] ) + SUM ( 'Table'[M] ) )
RETURN
a / b
Here is my test result.
Thank you very much! it works 🙂
what do you want it to show, or do you want to not show a total?
Proud to be a Super User!
I want to show the results for each subunit and unit based on the selection. I use card to show the result, which is R column figure. When the use selects subunit it give right calculation. However when the user select unit, which should be total of sub-units, it gives wrong number.
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.