cancel
Showing results for
Did you mean:
Frequent Visitor

## Replicate excel table on power 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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Replicate excel table on power bi

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
New Contributor

## Re: Replicate excel table on power bi

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

Microsoft

## Re: Replicate excel table on power bi

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Replicate excel table on power bi

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.

Microsoft

## Re: Replicate excel table on power bi

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements