Forum Discussion
How to Top N output for multiple variables/columns
Looking to answer the question - By YEAR (ex.2020), what is the top destinations by sales for PRODUCT (A) from ORIGIN(LA)?
want the results for multiple Years, Products and Origins, sample data below.
I was looking at the formula TopN but I couldn't find an example that didn't have a year and multiple products attached. Do I need to keep building tables in the background by year and product and origin - that seems like an excessive amount of background tables as I want to be able to keep adding years and sorting this automatically and don't want to manually create tables in the background.
I do have filters on Report for Year and Product that I was thinking I could tie into somehow.
The sales by destination don't need to be summed, they are unique.
Any help, tips, tricks and suggestions would be appreciated.
Thank you in advance
_____
Data example:
Year Product Origin Destination Sales
2020 A LA AB 10
2020 A LA FL 20
2020 A LA CA 15
2020 A LA TX 30
2020 A NY AB 100
2020 A NY CA 200
2020 A NY FL 150
2020 A PR AB 50
2020 A PR LA 75
2020 B LA AB 15
2020 B NY CA 110
2020 B LA FL 220
2020 B LA TX 200
2020 C LA FL 50
2020 C NY FL 75
2020 C PR CA 100
2020 C NY TX 150
2020 C NY AB 25
2022 A LA CA 200
2022 A NY LA 20
2022 A PR FL 150
2022 B LA AB 225
2022 B NY FL 240
Example of a return table for top 2 destinations from each origin by sales:
Year Product Origin Destination Sales
2020 A LA TX 30
2020 A LA FL 20
2020 A NY CA 200
2020 A NY FL 150
2020 B LA FL 220
2020 B LA TX 200
2020 B NY CA 110
2020 C LA FL 50
2020 C NY TX 150
2020 C NY FL 75
2020 C PR CA 100
2022 A LA CA 200
2022 A PR FL 150
Hi Cloep8
You could use the WINDOW function to determine the Top N Destinations for each combination of Year/Product/Origin.
Sample PBIX attached.
Assuming a single table Sales contains your sample data and you have a Top N parameter that is returned by the measure Top N Value, you could create these measures:
Sales Sum = SUM ( Sales[Sales] )Sales Top N = VAR TopNValue = [Top N Value] VAR SummarizedSales = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Year], Sales[Product], Sales[Origin], Sales[Destination] ), "@Sales", [Sales Sum] ), ALLSELECTED ( ) ) VAR TopSales = WINDOW ( 1, ABS, TopNValue, ABS, SummarizedSales, ORDERBY ( [@Sales], DESC ), DEFAULT, PARTITIONBY ( Sales[Year], Sales[Product], Sales[Origin] ) ) VAR Result = CALCULATE ( [Sales Sum], KEEPFILTERS ( TopSales ) ) RETURN ResultAlternatively, you could use TOPN per combination of Year/Product/Origin to get the same result:
Sales Top N v2 = VAR TopNValue = [Top N Value] VAR TopDestinations = CALCULATETABLE ( GENERATE ( SUMMARIZE ( Sales, Sales[Year], Sales[Product], Sales[Origin] ), CALCULATETABLE ( TOPN ( TopNValue, VALUES ( Sales[Destination] ), [Sales Sum] ) ) ), ALLSELECTED ( ) ) VAR Result = CALCULATE ( [Sales Sum], KEEPFILTERS ( TopDestinations ) ) RETURN ResultWith these measures, the "Top N" is determined within the overall filter context of the visual. You can adjust the partitioning by changing the arguments of SUMMARIZE/PARTITIONBY.
I would also recommend creating a star schema-style model.
Does something like this work for you?
Hi,
PBI file attached.
Hope this helps.
Thanks for the reply from OwenAuger and Ashish_Mathur, please allow me to provide another insight.
Hi Cloep8 ,
You can use the following measure as a visual filter to filter out the top two sales in each group grouped by year, origin, and product.
Measure = VAR tb = CALCULATETABLE('Table', 'Table'[Year] = SELECTEDVALUE('Table'[Year]), 'Table'[Product] = SELECTEDVALUE('Table'[Product]), 'Table'[Origin] = SELECTEDVALUE('Table'[Origin]), ALLEXCEPT('Table','Table'[Year],'Table'[Product],'Table'[Origin]) ) VAR rank_ = RANKX(tb, CALCULATE(SUM([Sales]))) RETURN IF(rank_<3,1,0)Add this measure to the visual's filter, set to is 1.
Hopefully it will meet your needs.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- OwenAuger
Super User
Hi Cloep8
You could use the WINDOW function to determine the Top N Destinations for each combination of Year/Product/Origin.
Sample PBIX attached.
Assuming a single table Sales contains your sample data and you have a Top N parameter that is returned by the measure Top N Value, you could create these measures:
Sales Sum = SUM ( Sales[Sales] )Sales Top N = VAR TopNValue = [Top N Value] VAR SummarizedSales = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Year], Sales[Product], Sales[Origin], Sales[Destination] ), "@Sales", [Sales Sum] ), ALLSELECTED ( ) ) VAR TopSales = WINDOW ( 1, ABS, TopNValue, ABS, SummarizedSales, ORDERBY ( [@Sales], DESC ), DEFAULT, PARTITIONBY ( Sales[Year], Sales[Product], Sales[Origin] ) ) VAR Result = CALCULATE ( [Sales Sum], KEEPFILTERS ( TopSales ) ) RETURN ResultAlternatively, you could use TOPN per combination of Year/Product/Origin to get the same result:
Sales Top N v2 = VAR TopNValue = [Top N Value] VAR TopDestinations = CALCULATETABLE ( GENERATE ( SUMMARIZE ( Sales, Sales[Year], Sales[Product], Sales[Origin] ), CALCULATETABLE ( TOPN ( TopNValue, VALUES ( Sales[Destination] ), [Sales Sum] ) ) ), ALLSELECTED ( ) ) VAR Result = CALCULATE ( [Sales Sum], KEEPFILTERS ( TopDestinations ) ) RETURN ResultWith these measures, the "Top N" is determined within the overall filter context of the visual. You can adjust the partitioning by changing the arguments of SUMMARIZE/PARTITIONBY.
I would also recommend creating a star schema-style model.
Does something like this work for you?
- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
Thanks for the reply from OwenAuger and Ashish_Mathur, please allow me to provide another insight.
Hi Cloep8 ,
You can use the following measure as a visual filter to filter out the top two sales in each group grouped by year, origin, and product.
Measure = VAR tb = CALCULATETABLE('Table', 'Table'[Year] = SELECTEDVALUE('Table'[Year]), 'Table'[Product] = SELECTEDVALUE('Table'[Product]), 'Table'[Origin] = SELECTEDVALUE('Table'[Origin]), ALLEXCEPT('Table','Table'[Year],'Table'[Product],'Table'[Origin]) ) VAR rank_ = RANKX(tb, CALCULATE(SUM([Sales]))) RETURN IF(rank_<3,1,0)Add this measure to the visual's filter, set to is 1.
Hopefully it will meet your needs.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.