Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
SLI
Helper IV
Helper IV

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

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)

Capture.PNG

 

Thanks for your help

 

SLI

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@SLI 

Ooops! apologies. Here it goes..

PBIX File 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

13 REPLIES 13
v-lionel-msft
Community Support
Community Support

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.

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

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
Community Champion
Community Champion

@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].

fields.JPG

 

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"?). 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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
Community Champion
Community Champion

@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:

 

Top 20 simple.JPG

 

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!

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@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
Community Champion
Community Champion

@SLI 

 

OK, see if this is what you need:

Top 20 simple.JPG

 

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

regards

PaulDBrown
Community Champion
Community Champion

@SLI 

Ooops! apologies. Here it goes..

PBIX File 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Ashish_Mathur
Super User
Super User

Hi,

Share a simple dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

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))))

Top Brand.png

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/

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.