Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Solved! Go to 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
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
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
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
88 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |