summerize differently depending on filter

[Anonymous]

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

[tamerj1]

@Anonymous 

So this didn't work?

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

[Anonymous]

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

[tamerj1]

@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]
)

[Anonymous]

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

[tamerj1]

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

[Anonymous]

These are all codes I used in the visual

[tamerj1]

@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 

[Anonymous]

do you mean like that?

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

[tamerj1]

@Anonymous 

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

[Anonymous]

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?

[tamerj1]

@Anonymous 

Which table / column represents the costomer?

[Anonymous]

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

relationships are:

[tamerj1]

@Anonymous 

Please try

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

[Anonymous]

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

[tamerj1]

@Anonymous 
Please try

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

[Anonymous]

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?

[tamerj1]

@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 )
)
)

[Anonymous]

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

[tamerj1]

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

[Anonymous]

Oh this would be so great. How can we connect?