Forum Discussion

LUCASM's avatar
LUCASM
Helper IV
4 years ago

DIVIDE (SHARE)

I am trying to createa Label which shows the share of the top 50 Models over a total data set.

When I do this over the last month I have no problem

I have a calculation for this month 

 

 

C_Unit TW = CALCULATE(
SUM(
'My Table'[Sales Units]),
FILTER('My Table,'My Table'[Index_Month] = 1))

 

 

 

and a calculation for Top 50 This Month

 

 

C_Unit Top50 TW = CALCULATE(
    SUM('My Table'[Sales Units]),
        TOPN(50,
        GROUPBY('My Table',My Table[Short Name],My Table[Brand Group],My Table[Comparison]),
        CALCULATE(SUM('My Table'[Sales Units]))), FILTER ('My Table','My Table'[Index_Month] = 1))

 

 

 

I then DIVIDE one by the other

 

 

C_Unit Share TW = DIVIDE ([C_Unit Top50],[C_Unit TW])

 

 

 

 

My Problem is I need to do the same over 3 months BUT it MUST be based on the TOP 50 Products (aka Short Name) in the Current Month (aka Index_Month = 1) 

 

I think need to ammend this calcuation

 

 

C_Unit Top50 3M = CALCULATE(
    SUM('My Table'[Sales Units]),
        TOPN(50,
        GROUPBY('My Table',My Table[Short Name],My Table[Brand Group],My Table[Comparison]),
        CALCULATE(SUM('My Table'[Sales Units]))), 
        FILTER ('My Table','My Table'[Index_Month] = 1|| 'My Table'[Index_Month] = 2 ||'My Table'[Index_Month] = 3 ||'My Table'[C_Rev Top50 TW]))

 

 

 

 to include this filter

[Short Name] Top 50 by [C_Unit_TW]

but I'm struggeling to work out how to do this

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    LUCASM , Assume you need TOPN based on a measure M1, may be 1 month, 1 year or 3 months and you want to have values of the month based on that , say you have measure rolling 3 for that

     

    Try a measure like

     

    CALCULATE([Rolling 3],TOPN(50,Summarize(allselected('My Table') ,My Table[Short Name],My Table[Brand Group],My Table[Comparison]),[This Year],DESC),Summarize('My Table',My Table[Short Name],My Table[Brand Group],My Table[Comparison]))

     

    Rolling 3 can be like

    Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

    • LUCASM's avatar
      LUCASM
      Helper IV
      4 years ago

      I think there is something amis here

       

       

      CALCULATE(
[Rolling 3],
TOPN(50,
Summarize(allselected('My Table') ,My Table[Short Name],My Table[Brand Group],My Table[Comparison]),
[This Year],DESC),
Summarize('My Table',My Table[Short Name],My Table[Brand Group],My Table[Comparison]))

       

       

      [This year] does not exist either in the summarise above nor in my table

      I attempted to work out what you were doing and decided that you were sorting the table by Sales Units DESC to pick up the Top 50 by Sales Units

      But to do that I had to put the Sales Units into the SUMMARISE and the resulant number is way to low.

       

      I like the Rolling 3 calculation I do have a date field so I used that and the 3 month total is spot on.
      Now I still need to calculate the TOP 50 SUM([sales units]) but only include models that exist in the current month 

       

      This is what I did - not correct

       

       

      SUM_TOP_50_UNITS_3_MONTHS = 
CALCULATE([Rolling 3],
TOPN ( 50, SUMMARIZE(ALLSELECTED('My_Table'),My_Table[Short Name],'My_Table'[Brand Group],'My_Table'[Comparison],My_Table[Sales Units]),
'My_Table'[Sales Units],DESC),
SUMMARIZE('My_Table',My_Table[Short Name],My_Table[Brand Group],My_Table[Comparison]))