topic Re: How to calculate "upsell" for a retail company? in DAX Commands and Tips

How to calculate "upsell" for a retail company?

MK2023 — Sat, 16 Dec 2023 14:55:07 GMT

I need help writing correct DAX for the measure which calculates upsell amount.

I have 10 stores which sell bicycles and e-bicycles and other equipment. The stores are in lookup table "Look_MT" and they are in "Mjesto troška" column.

The product groups are in lookup table "Look_Grupe roba i usluga", sorted under "Grupa robe/usluge".

Data about the invoices is stored in "Real_FaktureMP" table, dates, invoice number ("#BrojIRA" column), products, store (under "Mjesto troška") and amounts (under "$978_MP iznos")

I need to calculate for each store, so I can later use the filter.

So, need to check for every invoice (in the table "Real_FaktureMP", under "#BrojIRA" column) that has sold something from the product group "Bicikli" or "e-Bicikli". If one of two (or both) products from that group are sold on that invoice, I need to sum the rest of the amount in the invoice.

Since the stores have same invoice numbers (from 1 to .....), I need to be carefull and check the store AND the invoice to make sure there is no duplicate. I also need to be carefull that sometimes both "Bicikli" and "e-Bicikli" are sold on the same invoice - I need to take into account the rest of the amount only once.

Is this clear enough?

The idea would be to motivate the staff to sell more extras to the people who come and buy a bicycle or e-bicycle. I need to find out for every day and every store how much in value that is.

Can any kind soul help? Asked ChatGPT too, no luck 😞

Thank you ahead,

Re: How to calculate "upsell" for a retail company?

some_bih — Sat, 16 Dec 2023 19:20:32 GMT

Pozdrav MK2023

Napiši na "našem" da vidimo šta se može uraditi 🙂

Osiguraj odgovarajući primjer, idealno u Excel csv sa očekivanim outputom... da brže završimo

Re: How to calculate "upsell" for a retail company?

Fowmy — Sun, 17 Dec 2023 10:04:14 GMT

MK2023

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

Re: How to calculate "upsell" for a retail company?

MK2023 — Sun, 17 Dec 2023 10:54:35 GMT

Pozdrav i hvala na javljanju. Primjer očekivanog outputa bi bio - na primjeru jednog dućana ("Mjesto troška") i za jedan određeni datum, zbroj iznosa koji su označeni crvenim. Dakle, ono što je prodano UZ bicikli ili e-bicikl (a to su "Grupe robe/usluge").

Možda je jasnije ako ovako stavim dnevnu situaciju iz pivotice?

Trebam izračunati taj iznos "$978_MP iznos" za svaki dan, odnosno račun ""#BrojIRA", za svaki dućan posebno, jer se brojevi u dućanima mogu poklapati. Ovdje su to samo računi broj 1475 i 1479 koje treba uzeti u obzir.

Hello and thank you for your response. An example of the expected output would be - on the example of one store ("Mjesto troška") and for one specific date, the sum of the amounts marked in red. So, what was sold WITH "BICIKLI" or "e-BICIKLI" (which are "Grupa robe/usluge").

Maybe it's clearer if I present the daily situation from the pivot like this?

I need to calculate that amount "$978_MP amount" for each day, that is for every invoice "#BrojIRA", for each store separately, because the numbers in the stores can match. Here it is only invoices 1475 and 1479 to consider.

Hvala 🙂

Thank you ...

Re: How to calculate "upsell" for a retail company?

some_bih — Mon, 18 Dec 2023 07:53:22 GMT

Hi / Pozdrav MK2023

We only have pictures, not structure per tables, still

to calculate "other" items per same invoice, if you have already detailed data for items aka product details, and there info is in table "Grupa robe/usluge", so possible solution in your case could be
to exclude "BICIKLI" ili "e-BICIKLI", like following measure. "Grupa robe/usluge" should be dimension for items / products

Meassure non-BICIKLE=
CALCULATE (
[YOUR MEASURE for Sales Amount],

Your table as dimension "Grupa robe/usluge" <> "BICIKLI" ||

Your table as dimension "Grupa robe/usluge" <> "e-BICIKLI"
)

Try and let us know if it is ok.

Re: How to calculate "upsell" for a retail company?

MK2023 — Tue, 19 Dec 2023 08:22:14 GMT

Thank you for trying to help. That looks like a good start.

However, I do not need to sum ALL the amounts without the groups "BICIKLI" and "e-BICIKLI".

I do need to sum them, but ONLY amounts of the products which were sold on the same invoice where "BICIKLI" and "e-BICIKLI" products were sold as well.

As in the example I enclosed, only invoices 1475 and 1479 should be taken into account.

In other words, I need to filter somehow only the invoice numbers ("#BrojIRA"), - for each store separately (store is under "Mjesto troška")-, because the numbers in the stores can match, where products for the groups "BICIKLI" and "e-BICIKLI" were also sold, and sum the rest of the amount (without "BICIKLI" and "e-BICIKLI").

Re: How to calculate "upsell" for a retail company?

some_bih — Tue, 19 Dec 2023 10:10:48 GMT

Hi MK2023 we need you to share file with example and expected output.