Power Pivot "Difference From" in Power BI

[weihoetee]

Hi,

How do I achieve the same functionality as Power Pivot "Difference From" in Power BI based on the example below? I need to calculate the difference between current record and previous record in the matrix table. I tried to create new measure but couldn't find the "Difference From" functionality there. Thanks in advance.

[weihoetee]

@V-lianl-msft , thanks a lot for your help, I modified a bit to include more filters

[weihoetee]

@V-lianl-msft , uploaded pbix here. 
I also noticed that the matrix sum up the values using the same Excel data.

[V-lianl-msft · Accepted solution]

Hi @weihoetee ,

Create a rank colunmn:

rank =
RANKX (
    FILTER (
        'Capex_Opex Recovery',
        EARLIER ( 'Capex_Opex Recovery'[Structure] ) = 'Capex_Opex Recovery'[Structure]
    ),
    'Capex_Opex Recovery'[Bid Request Version ],
    ,
    ASC,
    DENSE
)

Then create the measure:

Measure 2 =
VAR current_str =
    SELECTEDVALUE ( 'Capex_Opex Recovery'[Structure] )
VAR current_index =
    SELECTEDVALUE ( 'Capex_Opex Recovery'[rank] )
VAR current_way =
    SELECTEDVALUE ( 'Capex_Opex Recovery'[Way] )
RETURN
    CALCULATE (
        SUM ( 'Capex_Opex Recovery'[CAPEX/OPEX] ),
        FILTER (
            ALL ( 'Capex_Opex Recovery' ),
            'Capex_Opex Recovery'[Structure] = current_str
                && 'Capex_Opex Recovery'[rank] = current_index - 1
                && 'Capex_Opex Recovery'[Way] = current_way
        )
    )

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

[weihoetee]

@V-lianl-msft , thanks for the solution. What if the value 1 and value are dynamic as I'm using matrix table?

[V-lianl-msft]

Hi @weihoetee ,

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?

How-to-provide-sample-data-in-the-Power-BI-Forum 

Please remove any sensitive data before uploading.

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

[amitchandak]

@weihoetee , You can not have diff between rows or current column and last column. Based on need, a measure needs to be created. Can you please explain the need with an example?

Appreciate your Kudos.

[weihoetee]

@amitchandak , thanks for dropping by. I attached a sample report above I done using Power Pivot. I tried to do the same report in Power BI, but I couldn't continue further as "Difference From" functionality in Power Pivot does not exist in Power BI. Basically the "Changes in Capex Opex" derived based on the difference of values in version 0.1 versus 1.0.

I need to achieve something that can highlight any difference between each version. It could be 0.1 versus 1.0, the next difference would be 1.0 versus 2.0 and so on

[V-lianl-msft]

Hi @weihoetee ,

Try to create measure like this:

Measure 2 = 
var sum_1 = 2*SUM('Table'[value.2])
var sum_all = CALCULATE(SUM('Table'[value.2]),ALLEXCEPT('Table','Table'[product]))
return IF(MAX('Table'[version])=1,sum_1-sum_all)

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