Hi, I have below below data in Matrix View
Day0 Day1 Day2 Day3
Amount 200 230 195 220
I want one more row in matrix view as
% Diff from prev value, final output I want like
Day0 Day1 Day2 Day3
Amount 200 230 195 220
% Diff 15.0% -15.2% 12.8%
Can anyone suggest how easily I can achieve this.
In other Visualization tool it was someting like get previous value and do a % calculation. In Power BI , I am not able to proceed.
NOTE: Below view I want in Matrix Visualization in Power BI
Thanks in Advance !
Solved! Go to Solution.
Day has to be numeric for this
as you want to have Measures in rows you actually will need a new table for that, you can create one in DAX
RowLabels = UNION(ROW("Column","Amount"),ROW("Column","Difference"))
then this measure will work
Measure = VAR __Amount = SUM('Table'[Amount]) VAR __DayIndex = MAX('Table'[Day]) VAR __CurrentDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex) VAR __PreviousDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex-1) VAR __Difference = DIVIDE(__CurrentDay-__PreviousDay,__PreviousDay) VAR __RowLabel = SELECTEDVALUE(RowLabels[Column]) RETURN SWITCH(__RowLabel, "Amount", __Amount, "Difference", __Difference, BLANK() )
Proud to be a Super User!
Here DayIndex-1 is throwing error , I think for the very first index its giving -ve index which is not there.
VAR __PreviousDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex-1)
Kindly share how to handle that because PreviousDay value for first index i want as blank and same for difference column as well. For fisrt Day difference should come as blank
Hi,
Below is the solution screenshot for your requirement.
Click Here to download the sample application.
Hope it help you.
Regards,
Pavan Vanguri.
Hi,
Below is the solution screenshot for your requirement.
Click Here to download the sample application.
Hope it help you.
Regards,
Pavan Vanguri.
Thanks,
I am able to get the %diff value when I am keeping date filed in column since e have used Max Date concept here. But how i can Month name, quarter , Fin Year etc in a hierarchy and put that in column. And based on lower drill down value ( in this case it is month ) I will get % diff . How a dynamic calculation we can do ?
can you share the layout of underlying data table?
Proud to be a Super User!
Hi I have data format something like this.
User Day Amount
U1 Day0 1000
U2 Day2 3000
U2 Day1 234
U3 Day0 345
Day has to be numeric for this
as you want to have Measures in rows you actually will need a new table for that, you can create one in DAX
RowLabels = UNION(ROW("Column","Amount"),ROW("Column","Difference"))
then this measure will work
Measure = VAR __Amount = SUM('Table'[Amount]) VAR __DayIndex = MAX('Table'[Day]) VAR __CurrentDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex) VAR __PreviousDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex-1) VAR __Difference = DIVIDE(__CurrentDay-__PreviousDay,__PreviousDay) VAR __RowLabel = SELECTEDVALUE(RowLabels[Column]) RETURN SWITCH(__RowLabel, "Amount", __Amount, "Difference", __Difference, BLANK() )
Proud to be a Super User!
Here DayIndex-1 is throwing error , I think for the very first index its giving -ve index which is not there.
VAR __PreviousDay = CALCULATE(SUM('Table'[Amount]),'Table'[Day]=__DayIndex-1)
Kindly share how to handle that because PreviousDay value for first index i want as blank and same for difference column as well. For fisrt Day difference should come as blank
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
User | Count |
---|---|
218 | |
58 | |
50 | |
47 | |
45 |
User | Count |
---|---|
268 | |
211 | |
113 | |
82 | |
71 |