Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I have a sales table which could look like this
ticket id | product id | date | category | subcategory | Revenue |
1 | product1 | 01-01-2019 | category1 | subcategory1 | 10 |
1 | product2 | 02-01-2019 | category1 | subcategory1 | 5 |
1 | product3 | 03-01-2019 | category1 | subcategory2 | 5 |
2 | product4 | 04-01-2019 | category1 | subcategory3 | 20 |
2 | product5 | 05-01-2019 | category1 | subcategory3 | 10 |
2 | product6 | 06-01-2019 | category2 | subcategory4 | 50 |
3 | product7 | 07-01-2019 | category2 | subcategory5 | 5 |
3 | product8 | 08-01-2019 | category2 | subcategory6 | 10 |
3 | product9 | 09-01-2019 | category2 | subcategory7 | 10 |
I want to be able to build a table with category as a dimension and calculate a measure that would give for each category the nb of (distinct) tickets where the category has the highest total revenue in the ticket.
Let me give an example with the data from the above table.
here you see for each ticket which category has the highest total revenue.
ticket | category | total revenue in ticket | category has highest revenue in ticket? |
1 | category1 | 20 | Yes |
1 | category2 | 0 | No |
2 | category1 | 30 | No |
2 | category2 | 50 | Yes |
3 | category1 | 0 | No |
3 | category2 | 25 | Yes |
Based on that i obtain the table below (which is the one i'm looking for) giving the nb and % of tickets where the category has the highest total revenue.
category | nb of tickets where category has highest revenue in ticket | % |
category1 | 1 | 33% |
category2 | 2 | 67% |
Total | 3 | 100% |
Not trivial !
And to add a little more complexity, i would like to same measure to calculate either by category or subcategory depending on which dimension i use in the table.
Thanks for your help
Solved! Go to Solution.
@fabienrolland wrote:What i'd like is a measure that can calculate what you did but depending if i'm at the category level, it calculates the top n by category, and if i'm at the subcategory level it calculates topn by subcategory.
Is it possible to create a single dynamic measure like that, that can be used in a product hierarchy?
To do this you would have to test if the subcategory or category is currently selected. You would do this by checking the lowest level first, then work your way up
eg.
Test3 = var _allTickets = VALUES(Tickets[ID Ticket]) return IF( HASONEVALUE(Produits[Sub Famille]), var _topSubCat = GENERATE(_allTickets, TOPN(1, ALL(Produits[Sub Famille]), [CA YR N], DESC)) RETURN COUNTROWS(FILTER(_topSubCat, [Sub Famille] = SELECTEDVALUE(Produits[Famille]))) IF( HASONEVALUE(Produits[Famille]), var _topCat = GENERATE(_allTickets, TOPN(1, ALL(Produits[Famille]), [CA YR N], DESC)) RETURN COUNTROWS(FILTER(_topCat, [Famille] = SELECTEDVALUE(Produits[Famille]))) , COUNTROWS(_allTickets) ) )
Makes sense. So if i use a product hierarchy with 4 levels, i would start at the lowest level (say sku is level 4) then work my way up (level 3: line > level 2 : subcategory > level 1 : category). Is that right?
Thanks a lot!
Based on the data you provided you could calculate this with a measure like the following:
Nb of tickets where category has highest revenue in ticket = // Get all tickets in the current filter context var _allTickets = VALUES(Sales[ticket id]) // get the top category for each ticket var _topCat = GENERATE(_allTickets, TOPN(1, ALL(Sales[category]), [Total Revenue], DESC)) // count the "selected" categories in the _topCat table variable return IF( HASONEVALUE(Sales[category]), COUNTROWS(FILTER(_topCat, [category] = SELECTEDVALUE(Sales[category]))), DISTINCTCOUNT(Sales[ticket id]) )
Thanks a lot
I transformed your formula slightly and it seems to work.
HOWEVER, as you can see from the below, i built a table with category (yello) and underneath each category i have subcategories. What i'd like is a measure that can calculate what you did but depending if i'm at the category level, it calculates the top n by category, and if i'm at the subcategory level it calculates topn by subcategory.
Is it possible to create a single dynamic measure like that, that can be used in a product hierarchy?
@fabienrolland wrote:What i'd like is a measure that can calculate what you did but depending if i'm at the category level, it calculates the top n by category, and if i'm at the subcategory level it calculates topn by subcategory.
Is it possible to create a single dynamic measure like that, that can be used in a product hierarchy?
To do this you would have to test if the subcategory or category is currently selected. You would do this by checking the lowest level first, then work your way up
eg.
Test3 = var _allTickets = VALUES(Tickets[ID Ticket]) return IF( HASONEVALUE(Produits[Sub Famille]), var _topSubCat = GENERATE(_allTickets, TOPN(1, ALL(Produits[Sub Famille]), [CA YR N], DESC)) RETURN COUNTROWS(FILTER(_topSubCat, [Sub Famille] = SELECTEDVALUE(Produits[Famille]))) IF( HASONEVALUE(Produits[Famille]), var _topCat = GENERATE(_allTickets, TOPN(1, ALL(Produits[Famille]), [CA YR N], DESC)) RETURN COUNTROWS(FILTER(_topCat, [Famille] = SELECTEDVALUE(Produits[Famille]))) , COUNTROWS(_allTickets) ) )
Makes sense. So if i use a product hierarchy with 4 levels, i would start at the lowest level (say sku is level 4) then work my way up (level 3: line > level 2 : subcategory > level 1 : category). Is that right?
Thanks a lot!
@fabienrolland wrote:Makes sense. So if i use a product hierarchy with 4 levels, i would start at the lowest level (say sku is level 4) then work my way up (level 3: line > level 2 : subcategory > level 1 : category). Is that right?
Yes, that is correct.
The reason for this is that a filter at lower level creates an implied filter in the "parent" levels.
So in a structure like the following:
Level1 Level2
Bikes Mountain Bikes
Bikes Road Bikes
Accessories Helmets
A filter on Level2 = "Road Bikes" will create an implied filter of Level="Bikes", so you should always check for filters from the lower levels first
Hi @fabienrolland ,
I'd like to suggest you to take a look at following blog about measure calculate on total level:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |