Replicate excel table on power bi

[jerryhpe_Bi]

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

[v-yulgu-msft]

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

[jerryhpe_Bi]

Hi @v-yulgu-msft

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.

[v-yulgu-msft · Accepted 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

[jthomson]

Guessing that this is to prevent any division by zero from causing major breakage, the DIVIDE function is what you'd be after