Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Nickodemus
Helper III
Helper III

Use different values in measure dependent on a user defined preference

I have fields in my data as follows:

ProductCost ACost B
One100110
Two400450
Three300320
Four500575
Five9001010

 

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:

ProductCost
One100
Two400
Three300
Four575
Five900
TOTAL2275

 

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

ProductCost
One110
Two450
Three300
Four575
Five900
TOTAL2335

 

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.

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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:

image.png

Hopefully this is what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

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:

image.png

Hopefully this is what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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:

image.png

Hopefully this is what you are looking for.

 

Regards,

Tom

 


 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.