Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
This requirement effectively goes against the foundations of tabular models, but interested if anyone has a solution. What I'd like to do is apply a filter on a column and have that filter context apply to another column at a higher level to dynamically calculate level of detail totals. Take the below dataset as example:
Category | Brand | Product | Sales |
Food | Oreo | A | 10 |
Food | Heinz | B | 20 |
Food | XYZ | C | 30 |
Beverage | Coke | D | 15 |
Beverage | Pepsi | E | 25 |
Beverage | XYZ | F | 35 |
What I'd like to do is allow users to apply a filter on Brand where, for example, Brand = 'XYZ' and it would return Category sales which would sum Food + Beverage to be 135 because Brand XYZ exists in both categories. In comparison, if I were to filter where Brand = 'Pepsi' then the Category sales measure would just return 75 for Beverage. The reason I cannot use an ALLEXCEPT() is because I don't want to force a selection on category, I want to be able to solely place a filter selection on Brand and have that infer the Category totals whether the mapping is one-to-one or one-to-many.
The goal ultimately is to be able to allow users to interact with a minimum number of filters while still being able to look at totals and averages across a category without having to explicitly select category in a filter. Thanks!
Solved! Go to Solution.
Hey @BUserTG ,
sure, that is possible.
Check the following measure:
Sales Category =
VAR vSummarizeTable =
ADDCOLUMNS(
VALUES( myTable[Category] ),
"@Sales",
CALCULATE(
SUM( myTable[Sales] ),
ALL( myTable[Brand] )
)
)
VAR vResult =
SUMX(
vSummarizeTable,
[@Sales]
)
RETURN
vResult
Hi @BUserTG,
Did selimovd 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestion to help others who faced similar requirements to find it more quickly.
If these not help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hey @BUserTG ,
sure, that is possible.
Check the following measure:
Sales Category =
VAR vSummarizeTable =
ADDCOLUMNS(
VALUES( myTable[Category] ),
"@Sales",
CALCULATE(
SUM( myTable[Sales] ),
ALL( myTable[Brand] )
)
)
VAR vResult =
SUMX(
vSummarizeTable,
[@Sales]
)
RETURN
vResult
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |