Forum Discussion

jerryhpe_Bi's avatar
jerryhpe_Bi
New Member
8 years ago
Solved

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

General Comment
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

4 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    8 years ago

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      jerryhpe_Bi
      New Member
      8 years ago

      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's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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