Forum Discussion
Solved
Repeat rank value through hierarchy levels
Hello community, I have a product table with different sizes and locations (Table on the left). I have created a ranking for this table based on Product name total sales (Table on the right) ...
JoseCamp The file is attached below my signature:
Product Rank = IF ( ISINSCOPE ( Jose[Product name] ), VAR CurrentProduct = SELECTEDVALUE ( Jose[Product Name] ) VAR TotalSellAtCurrentProduct = CALCULATE ( [Total Sell], Jose[Product name] = CurrentProduct, ALLEXCEPT ( Jose, Jose[Date] ), ALLSELECTED ( Jose[Date] ) ) VAR TotalSellEachProduct = CALCULATETABLE ( ADDCOLUMNS ( VALUES ( Jose[Product Name] ), "@Total Sells", [Total Sell] ), ALLEXCEPT ( Jose, Jose[Date] ), ALLSELECTED ( Jose[Date] ) ) VAR RankCurrentSell = FILTER ( TotalSellEachProduct, [@Total Sells] >= TotalSellAtCurrentProduct ) VAR Result = COUNTROWS ( RankCurrentSell ) RETURN Result )
AntrikshSharma thanks for your answer! I should have been a bit more specific about my request! (My bad) The product table has selling dates and I need the ranking to change based on the sales of a specific date range, or if a selection of a specific location (s), or a group (s) of products.
| Product name | Size | Location | Sells | Date |
| Prod1 | S1 | Loc1 | $ 10.0 | 2021-03-10 |
| Prod1 | S2 | Loc1 | $ 20.0 | 2021-03-10 |
| Prod1 | S1 | Loc3 | $ 30.0 | 2021-03-12 |
| Prod1 | S1 | Loc4 | $ 20.0 | 2021-03-09 |
| Prod2 | S3 | Loc1 | $ 5.0 | 2021-03-10 |
| Prod2 | S4 | Loc4 | $ 8.0 | 2021-03-10 |
| Prod3 | S5 | Loc2 | $ 4.0 | 2021-03-10 |
| Prod3 | S5 | Loc3 | $ 3.0 | 2021-03-09 |
| Prod3 | S5 | Loc4 | $ 9.0 | 2021-03-09 |
Thanks for your help!
- AntrikshSharma
Community Champion
JoseCamp The file is attached below my signature:
Product Rank = IF ( ISINSCOPE ( Jose[Product name] ), VAR CurrentProduct = SELECTEDVALUE ( Jose[Product Name] ) VAR TotalSellAtCurrentProduct = CALCULATE ( [Total Sell], Jose[Product name] = CurrentProduct, ALLEXCEPT ( Jose, Jose[Date] ), ALLSELECTED ( Jose[Date] ) ) VAR TotalSellEachProduct = CALCULATETABLE ( ADDCOLUMNS ( VALUES ( Jose[Product Name] ), "@Total Sells", [Total Sell] ), ALLEXCEPT ( Jose, Jose[Date] ), ALLSELECTED ( Jose[Date] ) ) VAR RankCurrentSell = FILTER ( TotalSellEachProduct, [@Total Sells] >= TotalSellAtCurrentProduct ) VAR Result = COUNTROWS ( RankCurrentSell ) RETURN Result )