cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: SUM SALES WITH PROMO

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. 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Marcox28 Regular Visitor
Regular Visitor

Re: SUM SALES WITH PROMO

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

 

 

 

 

Super User
Super User

Re: SUM SALES WITH PROMO

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.

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

shebr Member
Member

Re: SUM SALES WITH PROMO

I bet there is a hidden filter somewhere.

Marcox28 Regular Visitor
Regular Visitor

Re: SUM SALES WITH PROMO

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?

Moscuba Member
Member

Re: SUM SALES WITH PROMO

@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

 

 

Highlighted
Moscuba Member
Member

Re: SUM SALES WITH PROMO

@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

 

 

Super User
Super User

Re: SUM SALES WITH PROMO

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

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Marcox28 Regular Visitor
Regular Visitor

Re: SUM SALES WITH PROMO

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

Super User
Super User

Re: SUM SALES WITH PROMO

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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 297 members 3,219 guests