calculation from specific rows and columns

[Anonymous]

Dear team, 

Please help me with this problem scenario

Parameter                  Target          Actual 

A                                   15               12

B                                   18               14

C                                   10               11

D                                   15               16

Please note that this is a dummy data, the actual data set may have 100+ such rows

Formula = (Sum of B and C Target Value)/( Sum of A and D actual value),

 = ( 18+10)/(12+16) = 1

Note: the formula output has to be displayed on a card.

Thanks!

[edhans · Accepted solution]

This will work, returning 1 in a card.

Measure = 
VAR varNumerator = 
SUMX(
    FILTER(
        'Table',
        'Table'[Parameter ] = "B" || 'Table'[Parameter ] = "C"
    ),
    'Table'[Target         ]
)
VAR varDenomenator = 
SUMX(
    FILTER(
        'Table',
        'Table'[Parameter ] = "A" || 'Table'[Parameter ] = "D"
    ),
    'Table'[Actual ]
)
VAR Result = 
    DIVIDE(
        varNumerator,
        varDenomenator,
        0
    )
RETURN
    Result

Note: The field names have a bunch of spaces. I didn't realize that until I was well into doing the measure and wasn't going to go back and fix the source data. If you need further help, please see the links below for supplying data.

If this works for you, please mark it the solution.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could unpivot the "Target" and "Actual" columns in the query editor.

Then use filter to get the result.

Measure =
VAR a =
    SUMX (
        FILTER (
            'Table',
            'Table'[Parameter] IN { "B", "C" }
                && 'Table'[Attribute] = "Target"
        ),
        'Table'[Value]
    )
VAR b =
    SUMX (
        FILTER (
            'Table',
            'Table'[Parameter] IN { "A", "D" }
                && 'Table'[Attribute] = "Actual"
        ),
        'Table'[Value]
    )
RETURN
    DIVIDE ( a, b )

Here is my test file for your reference.

[Ashish_Mathur]

Hi,

This measure works

Measure = CALCULATE(SUM(Data[Target]),Data[Parameter]="B"||Data[Parameter]="C")/CALCULATE(SUM(Data[Target]),Data[Parameter]="A"||Data[Parameter]="D")

Hope this helps.

[edhans · Accepted solution]

This will work, returning 1 in a card.

Measure = 
VAR varNumerator = 
SUMX(
    FILTER(
        'Table',
        'Table'[Parameter ] = "B" || 'Table'[Parameter ] = "C"
    ),
    'Table'[Target         ]
)
VAR varDenomenator = 
SUMX(
    FILTER(
        'Table',
        'Table'[Parameter ] = "A" || 'Table'[Parameter ] = "D"
    ),
    'Table'[Actual ]
)
VAR Result = 
    DIVIDE(
        varNumerator,
        varDenomenator,
        0
    )
RETURN
    Result

Note: The field names have a bunch of spaces. I didn't realize that until I was well into doing the measure and wasn't going to go back and fix the source data. If you need further help, please see the links below for supplying data.

If this works for you, please mark it the solution.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum