Aggregated Matrix based on multiple parameters

[Paiman89]

Hi Everyone,

Need somehelp on how to create aggregated matrix based on multiple parameters.

here's the raw data, you can download the excel here

raw data

I need to aggregate it by averaging all the OA, QtyDus, Amt by slicers month,

then I'd like to show the aggregated result filtered by parameter OA & QtyDus, as you can see LI0288 won't show because avg_QtyDus = 27,592 < Param Dus = 40,000

below is the expected matrix result.

any help step by step on dax would be appreciated

thanks a lot

warm regards

Adrian

[Anonymous · Accepted solution]

Hi @Paiman89 ,

Below is my table:

The following DAX might work for you:

Avg_Amt = 
     AVERAGE(Table2[Amt])
Avg_OA = 
    var _pre = AVERAGE(Table2[OA])
    var _par = SELECTEDVALUE(QA[QA])
    var result = IF(_pre > _par , _pre , BLANK())
    return result
Avg_QTYDus = 
    var _pre = AVERAGE(Table2[QtyDus])
    var _par = SELECTEDVALUE(Dus[Dus])
    var result = IF(_pre > _par , _pre , BLANK())
    return result

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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