Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
jerryhpe_Bi
New Member

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

Hi @jerryhpe_Bi,

 

1.PNG 

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:

3.PNG

 

Sample data.

4.PNG

 

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.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @jerryhpe_Bi,

 

Do "Last1" and "current1" represent the first row in above sample table?

1.PNG

 

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.

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.

Hi @jerryhpe_Bi,

 

1.PNG 

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:

3.PNG

 

Sample data.

4.PNG

 

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.
jthomson
Solution Sage
Solution Sage

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors