Forum Discussion

Catherine_Anne's avatar
Catherine_Anne
New Member
6 years ago
Solved

Filtering information in order to perform a calculation

Hi 

 

I have a number of tables that are giving me data that I need to perform calculations on and then combine and give each customer a 'score'. 

I have done a number of calculations and I can use the slicer to give me a total score per customer per product but I am struggling to filter a score by product in Dax to enable a percentage calculation.  I need to factor in the product as the total possible instances are different by product.

 

For example:

If the customer has had product 1 then their 'total score' should be divided by 100 to give the percent.

If the customer has had product 2 then their 'total score' should be divided by 200 to give the percent.

 

How can I reference this and make my calculations work?

 

Thanks

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Catherine_Anne ,

     

    Try something like this:

    Measure =
VAR Customer_ =
    MAX ( 'Table'[Customer] )
VAR t =
    CALCULATETABLE ( VALUES ( 'Table'[Product] ), 'Table'[Customer] = Customer_ )
RETURN
    SWITCH (
        TRUE (),
        "Product 1" IN t, [Total Score] / 100,
        "Product 2" IN t, [Total Score] / 200
    )

     

     

    Best Regards,

    Icey

     

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

2 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Catherine_Anne ,

     

    Try something like this:

    Measure =
VAR Customer_ =
    MAX ( 'Table'[Customer] )
VAR t =
    CALCULATETABLE ( VALUES ( 'Table'[Product] ), 'Table'[Customer] = Customer_ )
RETURN
    SWITCH (
        TRUE (),
        "Product 1" IN t, [Total Score] / 100,
        "Product 2" IN t, [Total Score] / 200
    )

     

     

    Best Regards,

    Icey

     

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