How to find out if a product is in the top 20 of last month's turnover

[SLI]

Hi 

I have a list of products by store,

I want to know how to find out, if a product is in  the top 20 of last month's turnover

the idea is to add a column in the table that contains the list of products ,in wich i indicate by yes or no if the product was in top 20 of last month's turnover (or indicate its position in the top20)

Thanks for your help

SLI

[v-lionel-msft]

Hi @SLI ,

If @amitchandak ' answer can't solve your problem, please give a sample data model.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SLI]

Hi 

here is a sample data model

i want to add a column (yes/no) on the Number of box produced/h 's visual wich indicates if the product is in the top 20 turnover of last month 

sample data 

best regards

[SLI]

Any Idea?

---

@SLI wrote:

Hi 

here is a sample data model

i want to add a column (yes/no) on the Number of box produced/h 's visual wich indicates if the product is in the top 20 turnover of last month 

sample data 

best regards

---

[PaulDBrown]

@SLI 

Just to make sure I am understanding what you are looking for...In your "Hour table", you are filtering by FACT_Production [RefrenceProduit], whereas in your "top 20 month-1" table you are filtering by DIM_Product [Designation].

Is this correct? If so, you wish to identify FACT_Production [RefrenceProduit] (which is a subset of DIM_Product [Designation]) is in the top 20 of the previous month in what context?:
1)  if FACT_Production [RefrenceProduit] as en entity is in the top 20 vs all FACT_Production [RefrenceProduit] (vs the POS or overall)?

2)  If the DIM_Product [Designation], under which the FACT_Production [RefrenceProduit], is listed is in the top 20 (vs the POS or overall)? (in other words, if 2 products from FACT_Production [RefrenceProduit] listed in the visual are from the same DIM_Product [Designation], you want them both to have the same ranking, which is actually the ranking of the DIM_Product[Designation] in the previous month?)

And how do you define  "previous month"? you have a date slicer (day) and a report level slicer (establishing the last 30 days...So the previous month is when comparing to which slicer? Both?, the single date?, the month previous to the "last 30 days"?). 

[SLI]

Hi Paul and thank you for your reply

i have 3 dimension's tables (dim_time, dim_product and dim_pointofsale) and 1 fact table (fact_production) in wich i have sales by product, date and point of sale

i have a relation between the fact table and other dimensions (fact_production is related to the dim-product by the column produits

i have also 2 slicers the point of sale wich permit to select the point of sale (pos) and date in wich single date choosen

the thsales of last month is calculated using the date selected in the slicer 

THSalesp M-1 = calculate (sum (fact_production [sales]), previousmonth (dim_time [date]))

example if i choose '2020-02-19' the thsales m-1 will give me the turnover of January 2020

i would like to know if the product in the "Hour Table" is in the top 20 of the THSalesp M-1 (or simply indicate its rank in the top 20 of last month) based on the date and Point of sale  choosen in the slicer

thanks

[PaulDBrown]

@SLI 

Thanks for the explanation on "previous month". I'm still confused as to what you mean by "if the product in the "Hour Table" is in the top 20 of the THSalesp M-1" 

As I stated in my previous message, in your Hour Table (which is where you want to identify the ranking) you are filtering by FACT_Production [Reference Produit]. In the other table, "Top 20 Month -1" you are filtering by DIM_Product [Designation] (which is a parent to FACT_Production [Refrence Produit].

You see, in both tables your are calling different columns "Product", so I'm confused as to what exactly you are trying to show.

If what you need is simply the rank for the  "Top 20 Month -1". here it is:

But you mentioned you wanted this ranking to be identified in the Hour table, right? if so, do you want to show the ranking for the WHOLE DIM_Product [Designation category]? in other words, each value of the FACT_Production [Reference Produit], which corresponds to the Parent DIM_Product [Designation category], will be ranked as it's parent's rank? (irrespective of the POS??) 

or do you want the FACT_Production [Reference Produit] to be ranked itself? (within each POS or overall??)

Basically I am asking you to please define exactly what ranking you need in the "Hour Table" and if this ranking should be, and to specify if the ranking should be within the context of each POS or if it based on the sales for the whole period irrespective of the POS...

I'm confused!

[SLI]

@PaulDBrown 

effectively what i want is to specify the rankinf of the product in the FACT_Production [Reference Produit]  itself? (in the context of each POS )

Regards

[PaulDBrown]

@SLI 

OK, see if this is what you need:

I have attached the file. Please not that I have created a Dim table for the field FACT_Production [Reference Produit] which I have used in both the measure and the visual.

[SLI]

@PaulDBrown  thank you very much it is exactly the result wanted but i don't find the attached file!!

regards

[PaulDBrown · Accepted solution]

@SLI 

Ooops! apologies. Here it goes..

PBIX File 

[Ashish_Mathur]

Hi,

Share a simple dataset and show the expected result.

[amitchandak]

Create the last month measure like the example given below, you need to have a date dimension for that. Then drag product visual level filter and use advance top N filter and choose this measure

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[SLI]

Hi

here is an example of a data modelexample 

I want to add a column (yes / no) on the visual of the number of boxes produced / h which indicates if the product is in the top 20 of turnover last month

Best regards