The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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