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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Marcox28
Helper II
Helper II

SUM SALES WITH PROMO

I have a problem in calculating sales made with receipts with at least one product being promoted.

 

my sales table is

 

TVE_IDTVE_IdArticoloTVE_PriceTVE_CodicePromozioneTVE_IDScontrino
18389683,954441
28549782,03    1
385087510    1
485357627,99    2
585357627,99    2
681020327,99    2
785357627,99    3
881020327,994444
984975710    4
108383662,99    4
118383662,99    4
128383662,99    5
138551442,49    5
148226733,59    5
1583517519    5
1683663423,92    5
1783426138,5    6
188343086,474447
198549632,03    8
208549662,03    8
218549672,03    8
228549602,73    8
2384783727,9    9
2483433014,974449
2581582319,95    9

 

where is it

TVE_ID indicates sales line ID

TVE_price indicates the selling price

TVE_codicepromo indicates the code of the promotion

TVE_IDScontrino indicates the receipt number.

 

How can I calculate the sum of all receipts that contain at least one product in promotion 444?

 

I expect this result to come: promotional sales = 129.24

because it sum the following sales line

 

TVE_IDTVE_IdArticoloTVE_PriceTVE_CodicePromozioneTVE_IDScontrino
18389683,954441
28549782,03    1
385087510    1
881020327,994444
984975710    4
108383662,99    4
118383662,99    4
188343086,474447
2384783727,9    9
2483433014,974449
2581582319,95    9

 

44 REPLIES 44

Well, if the promo code is in a slicer, then you could do something like a MAX. If you want all the promo codes then you could simply repeat the procedure that was done, so something like:

 

Sum in Promo = 
VAR __promocodes = FILTER(VALUES(Table9[TVE_CodicePromozione]),[TVE_CodicePromozione]<>BLANK())
VAR __values = SELECTCOLUMNS(FILTER(table9,[TVE_CodicePromozione] IN __promocodes),"__SearchValues",[TVE_IDScontrino])
VAR __tmpTable = ADDCOLUMNS('Table9',"__In",IF([TVE_IDScontrino] IN __values,TRUE,FALSE))
RETURN SUMX(FILTER(__tmpTable,[__In]),[TVE_Price])

BTW, if you are a SQL guy trying to learn DAX, then the book that you want is @Phil_Seamark's fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence. It is written from exactly that perspective. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I think it also goes down to how you are intended to use the solution, if you are going to slice the data then measure calculation is different.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2kis 100% correct. If you use a slicer, then you will need to add some ALL or ALLSELECTED functions to the measure caculation presented. Or, you will need to have a disconnected table for your slicer with your promo codes. Good call @parry2k.

 

Unfortunately, because context is so important to DAX, it makes these kinds of issues very difficult to answer via just forum posting. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I tried to use your formula but without success,
my problem maybe is that tve_CodicePromozione is part of a filter. in fact, my sales table contains multiple codes.
can you give me an example of the insertion to insert the function all?

 

plus it gives me an error in using the IN function

@Marcox28- What version of Power BI Desktop are you on?

 

The new IN syntax has been available in Power BI since November 2016 and in Excel 2016 since Version 1701 (February 2017 in Current Channel). This syntax will be also available in future versions of Analysis Services (after SQL Server 2016).

 

An example of ALL might be something like this:

 

Sum in Promo = 
VAR __promocodes = FILTER(VALUES(Table9[TVE_CodicePromozione]),[TVE_CodicePromozione]<>BLANK())
VAR __values = SELECTCOLUMNS(FILTER(ALL(table9),[TVE_CodicePromozione] IN __promocodes),"__SearchValues",[TVE_IDScontrino])
VAR __tmpTable = ADDCOLUMNS(ALL('Table9'),"__In",IF([TVE_IDScontrino] IN __values,TRUE,FALSE))
RETURN SUMX(FILTER(__tmpTable,[__In]),[TVE_Price])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

i use last update powerbi july 2018

 

my measure formula is

 

Sum in Promo =
VAR __promocodes = LASTNONBLANK(Venduto[CodicePromozione];0)
VAR __values = SELECTCOLUMNS(FILTER(all(Venduto);[CodicePromozione] IN __promocodes);"__SearchValues";[IDScontrino])
VAR __tmpTable = ADDCOLUMNS(all(Venduto);"__In";IF(CONTAINS(Venduto;[IDScontrino]; __values);TRUE;FALSE))
var total = SUMX(FILTER(__tmpTable;[__In]);[Prezzo])
return total

 

but not visualize to table .

errore.JPG

 

 

 

the error stamp 

Seems like CONTAINS is working, not sure why IN isn't, try this variation:

 

Sum in Promo 2 = 
VAR __promocodes = FILTER(VALUES(Table9[TVE_CodicePromozione]),[TVE_CodicePromozione]<>BLANK())
VAR __tmpTable1 = ADDCOLUMNS(ALL(Table9),"__In1",IF(CONTAINS(Table9,[TVE_CodicePromozione],__promocodes),TRUE,FALSE))
VAR __values = SELECTCOLUMNS(FILTER(__tmpTable1,[__In1]),"__SearchValues",[TVE_IDScontrino])
VAR __tmpTable = ADDCOLUMNS(ALL('Table9'),"__In",IF([TVE_IDScontrino] IN __values,TRUE,FALSE))
RETURN SUMX(FILTER(__tmpTable,[__In]),[TVE_Price])

 

Use the __tmpTable1 and __values lines from this to replace your __values line in your formula. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler@@Marcox28@parry2k

 

Hi,

 

Here is a simple solution that should work:  Weakness, it does not create a Measure. It only allows Promo sales to be filtered. However, it could be used to create a Measure.

 

In the Data Table under Query Editor add a Calculated Column that pulls in the TVE_IDScontrino where there is a value in TVE_CodicePromozione. This will populate only the row with the Promo code. 

 

That is done by Selecting TVE_CodiceProm... Does Not Contain "" (hand type quotes) Then Select Column TVE_IDScontrino 

 

 

It will deliver Errors due to Nulls.

==>> Someone could help here as this delivers error messages due to Nulls.

 

Close and Apply and ignore the Null errors

 

Make a New Table under Modeling:

 

PROMO = SUMMARIZECOLUMNS ( Table9[TVE_IDScontrino],Table9[ID_INO_Promo],FILTER ( 'Table9', Table9[TVE_CodicePromozione] <> BLANK() ) )

 

This contains the distinct values in the New Column created above.

 

Link this table to the Data table via TVE_IDScontrino

 

Now you can use this table as a filter to only show the Discounted Sales.  In the Filter you can leave the Blanks in to show all other sales or unselect blanks and only show the Promo sales amount.

Capture.JPG

 

 

Anonymous
Not applicable

@Greg_Deckler@@Marcox28@parry2k

 

Hi,

 

Here is a simple solution that should work:  Weakness, it does not create a Measure. It only allows Promo sales to be filtered. However, it could be used to create a Measure.

 

In the Data Table under Query Editor add a Calculated Column that pulls in the TVE_IDScontrino where there is a value in TVE_CodicePromozione. This will populate only the row with the Promo code. 

 

That is done by Selecting TVE_CodiceProm... Does Not Contain "" (hand type quotes) Then Select Column TVE_IDScontrino 

 

 

It will deliver Errors due to Nulls.

==>> Someone could help here as this delivers error messages due to Nulls.

 

Close and Apply and ignore the Null errors

 

Make a New Table under Modeling:

 

PROMO = SUMMARIZECOLUMNS ( Table9[TVE_IDScontrino],Table9[ID_INO_Promo],FILTER ( 'Table9', Table9[TVE_CodicePromozione] <> BLANK() ) )

 

This contains the distinct values in the New Column created above.

 

Link this table to the Data table via TVE_IDScontrino

 

Now you can use this table as a filter to only show the Discounted Sales.  In the Filter you can leave the Blanks in to show all other sales or unselect blanks and only show the Promo sales amount.

Capture.JPG

 

 

Hello I applied your formula but I also had to change the last IN because it kept giving me error. now the error has changed

 

a table of multiple values ​​was specified while a single value was expected

 

Sum in Promo =
VAR __promocodes =
FILTER ( VALUES ( Venduto[CodicePromozione] ); [CodicePromozione] <> BLANK () )
VAR __tmpTable1 =
ADDCOLUMNS (
Venduto;
"__In1"; IF ( CONTAINS ( venduto; [CodicePromozione]; __promocodes ); TRUE; FALSE )
)
VAR __values =
SELECTCOLUMNS (
FILTER ( __tmpTable1; [__In1] );
"__SearchValues"; [IDScontrino]
)
VAR __tmpTable =
ADDCOLUMNS (
'Venduto';
"__In"; IF ( CONTAINS ( venduto; [IDScontrino]; __values ); TRUE; FALSE )
)
VAR total =
SUMX(FILTER(__tmpTable;[__In]);[Prezzo Vendita])
RETURN
total 

 

Cattura.JPG

 

 

 

 

I hate to blame this on a language pack issue but it sure seems like the version of Power BI is not acting the same as my version. Is it possible for you to upload your PBIX file? I can't make heads or tails of why this is working for me but not for you.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

unfortunately it is a pbx that connects directly to the tabular and I can not export everything. Is there a way I can help you understand my problem?

Oh, well that actually explains a lot actually. That is almost certainly why the IN syntax does not work. This would have been super helpful to know from the get-go as that essentially changes everything. There are a lot of functions that are not optimized for Direct Query and thus do not function as expected:

 

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d...

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

oh wow!
and then how do we solve it with these compatibility?
I generated a disaster here on the forum 🙂

Anonymous
Not applicable

MArco,

 

I postred a solution today using a calculated table that will update with the data updates. It worked beautifully for me. You had one of the best people on the case and it seems the Desktop version is not capable of some new capabilities. That's why it's free!

Hello
thanks for the solution but it's not my chance because I have a dynamic connection to a tabular database behind power bi. I certainly do not have a power bi free but the pro version and license sql enterprise 2016.
I have to find a solution without importing the data and creating new tables
Anonymous
Not applicable

Marco,

 

The calculated table wouolld be created within the BI report, not on your server. Try it. If it's incompatible Power BI will tell you it's no compatible with the data source (and ask to download the data so say no if you don't weant that). 

 

However, there are limitations to such methods. Actually, some of the measures that were delivered here create "views" or tables in the back ground. MAybe that's why it's not working. However, Power BI delivers to me a message when something I created is not compatible with dynamic data and it says the data needs to be loaded. 

for the amount of data I have to process, I can not use a dynamic connection. this mode does not allow me to create tables inside the
.pbx

They did just add a preview feature in July 2018 for hybrid models (both dynamics connection and imported/calculated tables) but it is in Preview so mileage may vary.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

This is one of those case when very little is known from landscape point of view like, data source, powerbi version , connection method and than it all become very hard to resolve the issue even thou issue seems to be resolved 🙂

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I bet there is a hidden filter somewhere.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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