Forum Discussion

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

DAX

DAX Calculation

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%"
        )
)

zibster.pbix18 KB

Jihwan_Kim

Super 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.pbix18 KB