Forum Discussion
All with one filter
Hi there,
I have Contoso sales data and I want to create a measure that show fix values (sum sales for color Red)
regardless the slicers selection, This measure dynamiclly change but I need fix values for Red
See image below
Thanks,
Oded Dror
Its seems this one solved the issue
Total Sales Fix Red =Calculate (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),All(Sales),filter(all('Product'[Color] ) ,'Product'[Color] ="Red"))
8 Replies
- amitchandakSuper User
Oded-Dror , Try like
return //change return
Calculate (SalesAmit,
'Product'[Color] ="Red")
or
return //change return
Calculate (SalesAmit,
filter(all('Product'[Color] ) , 'Product'[Color] ="Red")
if you do not want other dimensions to work use all(sales) also
- Oded-DrorHelper III
Amit,
Thank you for your response but your solution wont solve the issue when I select for example Year slicer = 2020 it show the same values as the other measure Total Sales Red
ThanksTotal Sales Fix Red =VAR SalesAmt = SUMX(Sales, Sales[Quantity] * Sales[Net Price])ReturnCalculate (SalesAmt,filter(all('Product'[Color] ) , 'Product'[Color] ="Red"))
- parry2kSuper User
Oded-Dror the underline problem with your dax measure is that you cannot have apply filter when you have value stored in the VAR, in other words, salesamt you have stored in var is not getting filtered for the color ="Red"
SalesAmt Measure = SUMX(Sales, Sales[Quantity] * Sales[Net Price]) Total Sales Fix Red = Calculate ([SalesAmt Measure], 'Product'[Color] ="Red" //filter(all('Product'[Color] ) , 'Product'[Color] ="Red") )✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- Oded-DrorHelper III
Parry,
Your solution is not working the measure continue to response to the selected slicersAnd it should show the total sales red measure value at all time
Total Sales Fix Red =Calculate (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),'Product'[Color] ="Red") - Oded-DrorHelper III
Its seems this one solved the issue
Total Sales Fix Red =Calculate (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),All(Sales),filter(all('Product'[Color] ) ,'Product'[Color] ="Red"))
- parry2kSuper User
Oded-Dror if you are filtering product color then then remove the filters and then apply the one you need.
Total Sales Fix Red = Calculate (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ), REMOVEFILTERS ( 'Product' ), 'Product'[Color] ="Red")✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
- parry2kSuper User
Oded-Dror awesome, my recent measure should have fixed that too, the measure you used, and it has one drawback, if you filter on any other attribute of the product, you will run into the same issue, let's product name or code or whatever, since you are only removing the filter from color, anyhow, it all depends on your use case, how flexible/scalable solution you want to develop.
In the end, you have what you need that's what matters. Cheers!!
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.