Forum Discussion

bkr's avatar
bkr
Helper I
2 years ago

Dynamic column based on slicer

Hi all,

 

I have a list of products with its launch date (when it was introduced) and the type of introduction, whether it is replacement of an existing product or supports additional growth:

 

ProductLaunchType
A01.01.2015Replacement product
B20.06.2017Growth product
C15.09.2022Replacement product
D30.12.2023Replacement product
E08.05.2019Growth product

 

Second, I have a table of sales per year:

ProductYearSales
A202310
B202320
C202330
D202315
E202325

 

Now I'd like to display the sales in that year, categorized by product Type.
However, I'd like to add a category "Old" considering all products that have a Launch date before a certain year (given by a slicer) as old products for which the categorization does not apply.

E.g. I set that slicer to 2018, then the expected result would be

SalesCategory
25Growth
45Replacement
30Old

 

I prepared a disconnected date table and also a disconnected table with one column with the 3 categories but now I'm stuck.

Any help is highly appreciated, thanks!

Need Help

2 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    bkr 

    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! 🤠

     

     

     

     

     

    • bkr's avatar
      bkr
      Helper I
      2 years ago

      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

      SalesCategory
      25Growth
      45Replacement
      30Old

       

      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!