Calculating a Ratio

[Mal_Sondh]

Hi All,

I have data from a single table that looks something like the attached and i need to create a ratio for items 1 and 2, where the calculation is entry id 2/ entry id 1.

The unique keys are Entry ID, Home Id and Period.

 Ratio Calculation

I was thinking on creating a summary table with filters, but am i little lost.

Any ideas on how this can be done?

Any help would be appreciated.

Thanks

[v-alq-msft]

Hi,  @Mal_Sondh 

Based on your description, you may create a measure as below.

Result = 
var _entryid = SELECTEDVALUE('Table'[Entry ID])
var _homeid = SELECTEDVALUE('Table'[HomeId])
var _period = SELECTEDVALUE('Table'[Period])
var _value = SELECTEDVALUE('Table'[Value])
return
DIVIDE(
    _value,
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Entry ID] = _entryid+1&&
            'Table'[HomeId] = _homeid&&
            'Table'[Period] = _period
        )
    )
)

Result:

If I misunderstand your thoughts, please show me your expected result. Do mask sensitive data before uploading.

Best Regards

Allan

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

[Mal_Sondh]

Thanks everyone - @v-alq-msft i tried your method and it worked - however if i wanted to set the parameters to say just only ever calculate items 1 and 2 - where do i set this parameter?

I understand the following code --> When the entry id is 1, use entry id 2, however if i only want the parameters to be 1 and 2, how could i change the code to implement that?

"Table'[Entry ID] = _entryid+1&&"

Otherwise your code works and brings back the desired outcome.

[v-alq-msft · Accepted solution]

Hi, @Mal_Sondh 

You may create a calculated table and modify the measure as below.

Calculated table:
Table 2 = DISTINCT('Table'[Entry ID])

Measure:
Result = 
var _entryid = SELECTEDVALUE('Table'[Entry ID])
var _homeid = SELECTEDVALUE('Table'[HomeId])
var _period = SELECTEDVALUE('Table'[Period])
var _value = SELECTEDVALUE('Table'[Value])
return
IF(
    _entryid in FILTERS('Table 2'[Entry ID]),
    DIVIDE(
        _value,
        CALCULATE(
            SUM('Table'[Value]),
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Entry ID] = _entryid+1&&
                'Table'[HomeId] = _homeid&&
                'Table'[Period] = _period
            )
        )
    )
)

Then you need to use entry_id from Table2 as a slicer. Here are the results.

Best Regards

Allan

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

[Mal_Sondh]

Thanks!

[amitchandak]

@Mal_Sondh 

Not very clear , but try like

M1= calculate(sum(table[value]),table[Entity Id] ="1")
M2 =calculate(sum(table[value]),table[Entity Id] ="2")

M3= calculate(sum(table[value]),allexcept(table,table[Entity Id]))

M4 = divide([M1],[M2])
M5 = divide([M3],[M2])

Also refer https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390