topic Re: summerize differently depending on filter in DAX Commands and Tips

summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 08:31:50 GMT

Hi,
because my "Lost_amount"

does not summarize the total, I have created "Lost_amount3"

But depending on how many customers (=CYname) it summarizes differently.

Correct for one customer:

Something else instead of Total for more customers. (It is not even the average. No clue what it is summarizing)

Some ideas how this can be fix?

Thanks

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 08:42:09 GMT

Anonymous

So this didn't work?

https://community.powerbi.com/t5/DAX-Commands-and-Tips/wrong-total/m-p/3099511#M108904

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 10:04:46 GMT

Hi, thanks for your help again 🙂
I thought it works, but then I continued all night and now it is empty again. The columns did not change, the relationships did not change. I can't immagine why your code does not work not. So build up on your idea I used the summary table, which is now called "Lost_amount3", but this summarizes strange

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 10:04:43 GMT

Anonymous
This way should work

Lost_new = SUMX ( SUMMARIZE ( '6_Lost', '6_Lost'[StartDate], '6_Lost'[EndDate], '6_Lost'[Product], '6_Lost'[CYname], 'Date'[Jahr], 'Date'[Monat] ), [Last_Sales] )

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 10:09:00 GMT

This is empty too. Or do I have to add this code in front of something?

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 10:15:45 GMT

Anonymous
Please share a screenshot along with the dax code you wrote.

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 10:28:22 GMT

These are all codes I used in the visual

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 10:43:29 GMT

Anonymous
Try to have Jahr and Monat as real columns in the Date table and use them in the table visual and in the SUMMARIZE

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 10:58:39 GMT

do you mean like that?

I did change it in the visual too, but Lost_new is still empty

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 11:48:38 GMT

Anonymous

Would you please double check that these are the same columns that are used in the table visual one by one?

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 12:31:24 GMT

I double and triple checked and also created the visual new. But still empty

where
Jahr = YEAR ('Date'[Date])

Monat = MONTH('Date'[Date])

The code for "Lost_amount3"

Lost_amount3 = VAR __SummaryTable =

ADDCOLUMNS ( VALUES ( 'Date'[Date].[Monat]), "@value2", [Lost_amount] )

RETURN

SUMX ( __SummaryTable, [@value2] )

seems to summarize correct for one customer

Is it somehow possible to modify this?

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 13:15:05 GMT

Anonymous

Which table / column represents the costomer?

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 13:50:20 GMT

The customers are coming from a table, that selects column from table

relationships are:

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 14:04:54 GMT

Anonymous

Please try

Lost_amount3 =
SUMX (
CROSSJOIN ( VALUES ( 'Date'[Monta] ), VALUES ( 'Lost_prep'[Cliento] ) ),
[Lost_amount]
)

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 14:27:05 GMT

Hmmm, now it summarizes, but it has the wrong amount. Lost_amount should be the same as Lost_amount3

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 14:49:00 GMT

Anonymous
Please try

Lost_amount3 = SUMX ( GENERATE ( SUMMARIZE ( 'Date', 'Date'[Jahr], 'Date'[Monta] ), CALCULATETABLE ( VALUES ( 'Lost_prep'[Cliento] ) ) ), [Lost_amount] )

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 15:10:40 GMT

It summarized too much. So, I thought it would be better with a filter

but even this does not change something. The total still not a summary

Is this a relationship issue?

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 15:59:52 GMT

Anonymous

Not sure to be honest. Please try

Lost_amount3 =
SUMX (
GENERATE (
SUMMARIZE ( 'Date', 'Date'[Jahr], 'Date'[Monta] ),
CALCULATETABLE ( VALUES ( 'Lost_prep'[Cliento] ) )
),
CALCULATE (
[Lost_amount],
CROSSFILTER ( 'Date'[Date], '2_Abfrage_Orderline'[Date], BOTH )
)
)

Re: summerize differently depending on filter

Anonymous — Sun, 26 Feb 2023 16:16:27 GMT

Lost_amount3 does not match with Lost_amount. DAX seems to be very complicated

Re: summerize differently depending on filter

tamerj1 — Sun, 26 Feb 2023 16:45:44 GMT

Anonymous
It is better to connect and have a direct look at the data. Let me know if this is possible perhaps tomorrow noon Dubai time.