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 Team,
I am trying to replicate a table that is under an excel file its something like this:
Last Current
data 1 325 77
data 2 3199 1242
data 3 3149 1238
data 4 -13% -5%
data 5 -3% -3%
data 6 -7% -4%
the last 3 rows has this formula "=IFERROR((Last1-current1)/current1,"n/a")", can you tell me if its something that can be done or not?
Greetings
Solved! Go to Solution.
Hi @jerryhpe_Bi,
It is not possible to add percentage values under the "Sales" rows as shown above. As a workaround, we could create measures to calculate "compare month", add it to Matirx, extra columns would be displayed, like below:
Sample data.
Measure:
difference% = VAR PreviousSales = CALCULATE ( SUM ( Table7[Sales] ), FILTER ( ALL ( Table7 ), Table7[Category] = SELECTEDVALUE ( Table7[Category] ) && Table7[MonthNo] = MAX ( Table7[MonthNo] ) - 1 ) ) VAR CurrentSales = SUM ( Table7[Sales] ) RETURN IF ( ISBLANK ( PreviousSales ), BLANK (), ( PreviousSales - CurrentSales ) / CurrentSales )
Best regards,
Yuliana Gu
Hi @jerryhpe_Bi,
Do "Last1" and "current1" represent the first row in above sample table?
With these two values, how to get the desired result "-13%" and "-5%"?
=IFERROR((Last1-current1)/current1,"n/a") returns value (325-77)/77=3.22
Regards,
Yuliana Gu
Last represent the last month data and current is the month we are currently working. i think i can represent it better:
June July
"Sales done" 81 77
"compare month" 0% -5%
Where the formula works like this:
=IFERROR((Last1-current1)/current1,"n/a") returns value (77-81)/81=0.05
the problem that i am facing is that i need this data as shown on the last table all in the same matrix.
Do you know if that is even possible ?
Thanks for your help by the way.
Hi @jerryhpe_Bi,
It is not possible to add percentage values under the "Sales" rows as shown above. As a workaround, we could create measures to calculate "compare month", add it to Matirx, extra columns would be displayed, like below:
Sample data.
Measure:
difference% = VAR PreviousSales = CALCULATE ( SUM ( Table7[Sales] ), FILTER ( ALL ( Table7 ), Table7[Category] = SELECTEDVALUE ( Table7[Category] ) && Table7[MonthNo] = MAX ( Table7[MonthNo] ) - 1 ) ) VAR CurrentSales = SUM ( Table7[Sales] ) RETURN IF ( ISBLANK ( PreviousSales ), BLANK (), ( PreviousSales - CurrentSales ) / CurrentSales )
Best regards,
Yuliana Gu
Guessing that this is to prevent any division by zero from causing major breakage, the DIVIDE function is what you'd be after
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |