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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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