Forum Discussion

Paiman89's avatar
Paiman89
Regular Visitor
2 years ago
Solved

Aggregated Matrix based on multiple parameters

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

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.