Forum Discussion
Dynamic column based on slicer
OUTPUT :
step1 :
create a calculated table, with the added "old" row.
step2 :
link the new created table to product table
step 3 :
create the slicer as below :
step 4 :
create the measure :
Measure 3 =
var t = VALUES(product_type[Type])
var y = year(MAX(dimdate[Date]))
var ad =
ADDCOLUMNS(
t,
"sales" ,
SWITCH(
TRUE(),
SELECTEDVALUE(product_type[Type]) = "Old" ,
CALCULATE(
SUM(factsales[Sales]),
factsales[Year] <= y , REMOVEFILTERS(product_type[Type])
)
,
CALCULATE(SUM(factsales[Sales]))
)
)
RETURN
SUMX(
ad,
[sales]
)
Let me know if it works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Hi Daniel,
thanks for your reply. This almost works as intended.
Your solution nicely works out how much sales fall into the "old" category. It also shows how many of the "old" products are replacement products and how many are growth products.
However, I'd like to show how many of the "not old" products are replacement or growth.
In the example given above, the total should always add up to 100
>E.g. I set that slicer to 2018, then the expected result would be
| Sales | Category |
| 25 | Growth |
| 45 | Replacement |
| 30 | Old |
means: Out of the 100 bucks sales in 2018, I made 30 bucks with products older than 2018. Out of the remaining 70 bucks sales, 25 come from growth products introduced in 2018 or later and 45 come from replacement products introduced in 2018 or later.
I believe it's only a small step from your solution to what I need, but I'm still not able to fix the rest myself.
Thanks a lot!