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, 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() )
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?
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() )
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
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |