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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.