Percentage Calculation use the sum of one column but the distinct value of another column

[jessiewang]

Hi there,

Can someone help me creating a calculated column which use the Expected Qty. column's disctict value, but the sum of the Consumed Qty. column filtered by the same production order no. and item no.? See the bottom 4 lines. I would like the result of the 3 lines highlighted in yellow to be combined to a single line as the one at the bottom and the Consumed%=DIVIDE([Consumed Qty]-[Expected], [Expected], 0), but I could not figure out what DAX to use for the Consumed% column so that I can get only 1 line as the one at the bottom.

[Anonymous]

Hi @jessiewang 

Creating a single row directly from multiple rows is not possible.

However, you can implement data presentation by creating visuals.

Here's some dummy data

“Table”

Create columns.

column expectedQty = 
CALCULATE(
    MAX('Table'[Expected Qty]),
        FILTER(
            ALL('Table'), 
            'Table'[order no.] = EARLIER('Table'[order no.]) 
            && 
            'Table'[item no.] = EARLIER('Table'[item no.])
        )
    )

column consumedTotal = 
CALCULATE(
    SUM('Table'[Consumed Qty]),
        FILTER(
            ALL('Table'),
            'Table'[order no.] = EARLIER('Table'[order no.]) 
            &&
            'Table'[item no.] = EARLIER('Table'[item no.])
        )
    )

consumed % = 
DIVIDE(
    'Table'[_consumedTotal] - 'Table'[_expectedQty], 
    'Table'[_expectedQty], 
    0
)

I would recommend that you create MEASURES. Compared to COLUMN, MEASURE will take up less memory.

measure expectedQty = 
CALCULATE(
    MAX('Table'[Expected Qty]),
        FILTER(
            ALL('Table'), 
            'Table'[order no.] = MAX('Table'[order no.]) 
            && 
            'Table'[item no.] = MAX('Table'[item no.])
        )
    )

measure consumedTotal = 
CALCULATE(
    SUM('Table'[Consumed Qty]),
        FILTER(
            ALL('Table'),
            'Table'[order no.] = MAX('Table'[order no.]) 
            &&
            'Table'[item no.] = MAX('Table'[item no.])
        )
    )

Measure consumed % = 
 DIVIDE(
    'Table'[measure consumedTotal] - 'Table'[measure expectedQty],
    'Table'[measure expectedQty],
    0
)

Create a visual.

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jessiewang]

Thanks, Nono. I was using measures for Expected Qty and Consumed Qty. But I need to create slicers for Consumed%, so I cannot use measures for it, since measures cannot be used on slicers, only calculated columns can. I was able to combine multiple lines to one line in one of my other Power BI reports but not exactly the same scenario. The % is the most complicated thing in Power BI due to the evaluation context, when use different filters, the result may not be what you want to see. I will continue working on this issue. I'm able to get the result for Consumed% for that line I had in the screenshot. But I still have an issue for the Consumed% for the lines with Item No. TJSP-1LAM.