Use different values in measure dependent on a user defined preference

[Nickodemus]

I have fields in my data as follows:

Product	Cost A	Cost B
One	100	110
Two	400	450
Three	300	320
Four	500	575
Five	900	1010

I need to create a report which displays a matrix visual showing each product, and EITHER [Cost A], or [Cost B] dependent on the user preference BY PRODUCT. The user preference is then included in the aggregated total for the cost column of the matrix. For example:

User wants to use Cost A for all products except product 'Four', which should use Cost B:

Product	Cost
One	100
Two	400
Three	300
Four	575
Five	900
TOTAL	2275

Or perhaps the user wants to display the Cost B values for products One, Two and Four, which would give a different total:

Product	Cost
One	110
Two	450
Three	300
Four	575
Five	900
TOTAL	2335

The use of the matrix is required because my data can also be displayed by month/year, etc. However, the cost option selected for each product would carry throughout all time.

I have had a go at creating a slider for each product - each slider is specific to a single product and has the option of '1' or '0'. Selecting '0' would use Cost A, selecting '1' would use Cost B.... This works on the row but i can't get this to aggregate at the total level, and the maintenance of this will end up being enormous... we only have 60 products at the moment, but this could become more numerous very quickly.

The other option i've tried is to have a multi-select slicer for the products - the user would just need to tick the check boxes for the products to use Cost B, and a measure would handle which products are selected in the slicer, and therefore which cost to use.... i couldn't get this to work AT ALL!

Can anyone come up with another solution to this problem? I'd be extremely grateful.

[TomMartens · Accepted solution]

Hey @Nickodemus 

I prefer to create a multi select slicer that contains all the products, for this it's necessary to create a separate table, that is not related to the table that contains the Columns "Cost A" and "Cost B".

Then I created this measure:

Use Cost B if ticked = 
var checkIsFiltered = ISFILTERED('Products using Cost B'[Product])
var theBProducts = VALUES('Products using Cost B'[Product])
return
SUMX(
    VALUES('Fact'[Product])
    ,IF(AND(checkIsFiltered , 'Fact'[Product] in theBProducts)
        , CALCULATE(SUM('Fact'[Cost B]))
        , CALCULATE(SUM('Fact'[Cost A]))
    )
)

This allows to create something like this:

Hopefully this is what you are looking for.

Regards,

Tom

[Nickodemus]

Thank you very much for such a fast response!

It worked perfectly!! 

---

@TomMartens wrote:

Hey @Nickodemus 

I prefer to create a multi select slicer that contains all the products, for this it's necessary to create a separate table, that is not related to the table that contains the Columns "Cost A" and "Cost B".

Then I created this measure:

Use Cost B if ticked = 
var checkIsFiltered = ISFILTERED('Products using Cost B'[Product])
var theBProducts = VALUES('Products using Cost B'[Product])
return
SUMX(
    VALUES('Fact'[Product])
    ,IF(AND(checkIsFiltered , 'Fact'[Product] in theBProducts)
        , CALCULATE(SUM('Fact'[Cost B]))
        , CALCULATE(SUM('Fact'[Cost A]))
    )
)

This allows to create something like this:

Hopefully this is what you are looking for.

Regards,

Tom

---