DAX Cross Selling

[Anonymous]

 Hi to all,

I found a way to replicate cross selling from a different point of view.

First I have to count the sales slip (Ticket) that contain an original product group:

# Tickets Ori TV = 
CALCULATE(
	COUNTROWS(
		DISTINCT(
			SUMMARIZE(
				Datos ; Datos[Store] ; Datos[Ticket]
			)
		)
	);
	Product[FDP_key] IN { 121 ; 122 ; 124 ; 125 ; 129 ; 130 ; 132 ; 134 ; 126 ; 127 ; 128 ; 195 ; 133 ; 120 ; 123 ; 135 ; 136 ; 131 }
)

After that I count the tickets that contain an original and accesorie:

# Tickets Acc TV = 
CALCULATE(
	COUNTROWS(
		DISTINCT(
			FILTER(
				SUMMARIZE(
					Datos ; Datos[Store] ; Datos[Ticket]
				);
			[# Tickets Ori TV]
			)
		)
	);
	Product[FDP_key] IN { 147 ; 149 ; 150 ; 152 ; 165 ; 192 ; 203 ; 349 ; 3370 ; 3550 ; 3560 ; 3420 ; 3421 ; 3422 ; 3430 ; 3460 ; 3470 ; 3510 ; 442 ; 461 ; 462 ; 463 ; 464 ; 1350 ; 1382 ; 3121 ; 1357 ; 8370 }
)

This works perfectly only if you look at the measures at a global point of view:

 

If I make a table and want to see the data at product and ticket level I get this:

The measure for the accesories won't show up at the line with the product thas an accesorie.

Do you have an idea how I have to formulate the "# Tickets Acc TV" & "Net Sales Acc TV" in order to show the data at the table?

I leave you a link if you want to download the model adn try it for yourself:

Google Drive - Power BI Model

I hope you can help me 

Thx

[v-qiuyu-msft]

Hi @Anonymous,

What's your logic to calculate "# Tickets Acc TV" & "Net Sales Acc TV"? The measure you used is a little strange. If you remove the Art column from the table, then you can see the detail results of those two measures.

Best Regards,
Qiuyun Yu

[Anonymous]

Hi @v-qiuyu-msft

At the end I have to count the distinct combinations of a concatenate of Store and Ticket.

For the original products it's easy.

But for the accesories it is a bit more tricky, because I have to look for the tickets that contain an article thats original and one article thats an accessory.

I take take the original tickets and filter it by the accessory porduct groups.

I don't know if I have explaind myself very well...

[v-qiuyu-msft]

Hi @Anonymous,

Take the data in table Datos and Product for example, what's the kind of data you want to return in the table visual? What's the condition? Please show a picture of final result.

Best Regards,
Qiuyun Yu

[Anonymous]

HI @v-qiuyu-msft

At the end I want to know the amount of tickts with an original product and the amount tickets that have an original and an accessory article.

The table has to look like this: