Forum Discussion
Dynamic and Encompassing Slicer
I created a new table with just the values of the Sales Names, and left it disconnected.
Sale Names = VALUES('Table'[Sale Name])Then created the measure below:
Sale Cost (Measure) = var saleInProgressDates = CALCULATETABLE(VALUES('Table'[Sale In Progress]), TREATAS(VALUES('Sale Names'[Sale Name]), 'Table'[Sale Name])) RETURN CALCULATE(SUM('Table'[Sale Cost]), TREATAS(saleInProgressDates, 'Table'[Sale In Progress]))'Table' is the raw data you've provided, and 'Sale Names' is the table created above.
Result below:
I created a new table with just the values of the Sales Names, and left it disconnected.
Sale Names = VALUES('Table'[Sale Name])Then created the measure below:
Sale Cost (Measure) =
var saleInProgressDates = CALCULATETABLE(VALUES('Table'[Sale In Progress]), TREATAS(VALUES('Sale Names'[Sale Name]), 'Table'[Sale Name]))
RETURN CALCULATE(SUM('Table'[Sale Cost]), TREATAS(saleInProgressDates, 'Table'[Sale In Progress]))'Table' is the raw data you've provided, and 'Sale Names' is the table created above.
Result below:
vicky_ This is amazing! Thank you so much! Upon transposing this example to the actual dataset, the measure is collapsing one additional field which I've marked country in the raw data table. For example, selecting Sale A or C will hide Canada from the Matrix. Can the measure be adjusted to account for this or is this a limitation of PBI? Thank you again!
Sale Name Sale In Progress Sale Cost Country Sale A 1/10/2025 100 USA Sale A 1/11/2025 100 USA Sale A 1/12/2025 100 USA Sale A 1/13/2025 100 USA Sale B 1/11/2025 20 Canada Sale B 1/12/2025 20 Canada Sale C 1/12/2025 50 USA Sale C 1/13/2025 50 USA Sale C 1/14/2025 50 USA Hi,
will there always be a 1:1 relationship between the sale name and country? You can basically re-use the above measure, but change SUM('Table'[Sale Cost]) to MAX('Table'[Country]). Otherwise, you can place the country dimension under Sale Name in the heirarchy, and turn of the row sub-totals so that it looks a little cleaner.
