Problems with Context in Measures

[carolinastalo]

Hello all

I have this data structure:

Employee		Total Cost		Department
John		$      1.000		A
Phil		$      2.000		A
Mary		$          800		B
Daphne		$          700		B
James		$      3.000		C
Karen		$          500		D

An need to do these calculations:

1- Total Cost $8.000

TC = CALCULATE(SUM(Total Cost))

2- Support Department Cost (B and D) $2.000
SDC = CALCULATE (TC, DEPARTMENT IN ("B","D"))

3- Non-Support Department Cost (A and C) $6.000
N-SDC = CALCULATE (TC, DEPARTMENT IN ("A","C"))

4- RATIO = SDC / N-SDC    (33,3%)

5- Cost With Ratio = N-SDC * (1+RATIO)

I am getting these results:

Department		Total Cost		SDC		N-SDC	Ratio		Cost With Ratio
A		3000		0		3000			3000
B		1500		1500		0	Infinity		Infinity
C		3000		0		3000			3000
D		500		500		0	Infinity		Infinity
		8000		2000		6000	33,3%		8000

But I need to get these:

Department		Total Cost		SDC		N-SDC		Ratio		Cost With Ratio
A		3000		0		3000		33,33%		4000
B		1500		1500		0		33,33%		0
C		3000		0		3000		33,33%		4000
D		500		500		0		33,33%		0
		8000		2000		6000		33,33%		8000

How do I fix it?

[v-alq-msft · Accepted solution]

Hi, @carolinastalo 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create measures as below.

TC = SUM('Table'[Total Cost])

SDC = 
COALESCE(
    CALCULATE(
        SUM('Table'[Total Cost]),
        FILTER(
            'Table',
            'Table'[Department] in {"B","D"}
        )
    ),0
)

N-SDC = 
COALESCE(
    CALCULATE(
        SUM('Table'[Total Cost]),
        FILTER(
            'Table',
            'Table'[Department] in {"A","C"}
        )
    ),0
)

Ratio = 
DIVIDE(
    COALESCE(
        CALCULATE(
            SUM('Table'[Total Cost]),
            FILTER(
                ALL('Table'),
                'Table'[Department] in {"B","D"}
            )
        ),0
    ),
    COALESCE(
        CALCULATE(
            SUM('Table'[Total Cost]),
            FILTER(
                ALL('Table'),
                'Table'[Department] in {"A","C"}
            )
        ),0
    )
)

Cost With Ratio = [N-SDC]*(1+[Ratio])

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[veenashenolikar]

Hi @carolinastalo ,

Change your ratio measure as below:

Ratio% = CALCULATE(SUM(Table_Name[SDC])/SUM(Table_Name[N-SDC]),ALL(Sheet2))

Use % as data type here

And then update Cost with ratio measure as below:

Cost _ratio = sum(Sheet2[N-SDC]) * (1+[Ratio%])

If I helped resolve your issue please mark this as a solution

Regards,

Veena Shenolikar