Forum Discussion

zibster's avatar
zibster
Icon for Helper III rankHelper III
3 years ago
Solved

Percent from top

Hi, I am trying to get percentage in power Bi by dividing items designated as line 2 by the same items amount designated as line 1, and when there is no match of items like in scenario of Other the item amount would be divided by Total Line 1. The data table looks the same as the example without the percent's

 

LineItemSalesPercent
1Water6000 
1Orange5000 
1Apple5000 
1Total16000 
    
2Water5409.00%
2Orange2865.72%
2Apple97519.50%
2Other6504.06%

 

 

Thanks 

DAX
DAX Calculation
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Percent CC =
IF (
    Data[Line] = 2,
    VAR _lineonesales =
        SUMX (
            FILTER ( Data, Data[Item] = EARLIER ( Data[Item] ) && Data[Line] = 1 ),
            Data[Sales]
        )
    VAR _linetwosales = Data[Sales]
    VAR _lineonetotalsales =
        SUMX ( FILTER ( Data, Data[Item] = "Total" && Data[Line] = 1 ), Data[Sales] )
    VAR _result =
        DIVIDE ( _linetwosales, _lineonesales )
    RETURN
        FORMAT (
            IF (
                ISBLANK ( _result ),
                DIVIDE ( _linetwosales, _lineonetotalsales ),
                _result
            ),
            "#,#0.00%"
        )
)

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Percent CC =
IF (
    Data[Line] = 2,
    VAR _lineonesales =
        SUMX (
            FILTER ( Data, Data[Item] = EARLIER ( Data[Item] ) && Data[Line] = 1 ),
            Data[Sales]
        )
    VAR _linetwosales = Data[Sales]
    VAR _lineonetotalsales =
        SUMX ( FILTER ( Data, Data[Item] = "Total" && Data[Line] = 1 ), Data[Sales] )
    VAR _result =
        DIVIDE ( _linetwosales, _lineonesales )
    RETURN
        FORMAT (
            IF (
                ISBLANK ( _result ),
                DIVIDE ( _linetwosales, _lineonetotalsales ),
                _result
            ),
            "#,#0.00%"
        )
)

     

    • zibster's avatar
      zibster
      Icon for Helper III rankHelper III
      3 years ago

      Hi Jihwan,

       

      Would you have idea how to fix the % on the subtotal line?

       

      Thanks Z