nb of tickets within each product category where category has the highest revenue in the ticket

[fabienrolland]

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

[d_gosbell · Accepted 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) 
	)
)

[d_gosbell]

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

[fabienrolland]

Thanks a lot

I transformed your formula slightly and it seems to work.

Test3 =

var _allTickets = VALUES(Tickets[ID Ticket])

var _topCat = GENERATE(_allTickets, TOPN(1, ALL(Produits[Famille]), [CA YR N], DESC))

return

IF( HASONEVALUE(Produits[Famille]), COUNTROWS(FILTER(_topCat, [Famille] = SELECTEDVALUE(Produits[Famille]))), COUNTROWS(_allTickets) )

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?

[d_gosbell · Accepted 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) 
	)
)

[fabienrolland · Accepted solution]

 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!

[d_gosbell]

---

@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

[v-shex-msft]

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