cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jerryhpe_Bi Frequent Visitor
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 v-yulgu-msft
Microsoft

Re: Replicate excel table on power bi

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
jthomson New Contributor
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 v-yulgu-msft
Microsoft

Re: Replicate excel table on power bi

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.
jerryhpe_Bi Frequent Visitor
Frequent Visitor

Re: Replicate excel table on power 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.

Microsoft v-yulgu-msft
Microsoft

Re: Replicate excel table on power bi

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors