Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
This has probably been asked before with a different phrasing, so thank you and apologies in advance!
Let's say my data looks like this:
Item | Store | City | Stock |
A | High Street | London | 3 |
A | Low Street | Vancouver | 7 |
A | Main Street | Boston | 3 |
B | Jubilee Street | Toronto | 5 |
B | Fun Street | Rome | 7 |
C | Low Street | Vancouver | 3 |
C | High Street | London | 4 |
And my report shows the above data in various visuals (ex: Item count by City, number of stores by City, Distinct products by store, total stock by store/city, etc...).
I would like to create a Measure that:
1) SUMS the Stock
2) Item == A
3) Filters based on City if selected (ie: if you click on a City in a visual, the Measure is affected)
4) Does not filter based on anything else (ie: if you click on a Store in a visual, the Measure is not affected).
I suspect there's an ALLEXCEPT involved, but I have not been able to get it to work. Any hints?
Solved! Go to Solution.
Hi @lucazanzi,
You can try this method:
New a measure:
Total =
CALCULATE (
SUM ( 'Table'[Stock] ),
ALLEXCEPT ( 'Table', 'Table'[City] ),
FILTER ( 'Table', 'Table'[Item] = "A" )
)
And the result is:
Hope these help you.
Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @lucazanzi,
You can try this method:
New a measure:
Total =
CALCULATE (
SUM ( 'Table'[Stock] ),
ALLEXCEPT ( 'Table', 'Table'[City] ),
FILTER ( 'Table', 'Table'[Item] = "A" )
)
And the result is:
Hope these help you.
Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @lucazanzi
yes you are right. You csn use
=
CALCULATE ( SUM ( Data[Stock] ), ALLEXCEPT ( Date, Data[City] ) )
Alternatively, you can replace ALLEXCEPT with REMOVEFILTERS ( ), VALUES ( Date[City] )