Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

DAX Cross Selling

 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:

power bi2.png 

 

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

power bi.png

 

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 Smiley Happy

 

Thx

4 REPLIES 4
v-qiuyu-msft
Community Support
Community Support

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.

 

q3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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...

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.

 

q4.PNGq5.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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:

power bi3.png

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.