Forum Discussion

carolinastalo's avatar
carolinastalo
Frequent Visitor
5 years ago
Solved

Problems with Context in Measures

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-SDCRatio Cost With Ratio
A 3000 0 3000  3000
B 1500 1500 0Infinity Infinity
C 3000 0 3000  3000
D 500 500 0Infinity Infinity
  8000 2000 600033,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?

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

2 Replies

  • veenashenolikar's avatar
    veenashenolikar
    Helper V
    5 years ago

    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
     
     
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.