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      ...
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

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