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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.