Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
@V-lianl-msft , thanks a lot for your help, I modified a bit to include more filters
@V-lianl-msft , uploaded pbix here.
I also noticed that the matrix sum up the values using the same Excel data.
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.
@V-lianl-msft , thanks for the solution. What if the value 1 and value are dynamic as I'm using matrix table?
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.
@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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |