Division of 3 different columns

[IF]

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

[v-eachen-msft · Accepted 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.

[IF]

Thank you very much! it works 🙂

[vanessafvg]

what do you want it to show, or do you want to not show a total?

[IF]

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.