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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lost_flower
Helper III
Helper III

summerize differently depending on filter

Hi,
because my "Lost_amount"

lost_flower_2-1677399462514.png

 

does not summarize the total, I have created "Lost_amount3"
lost_flower_3-1677399485857.png

 

 

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

 

Correct for one customer:

lost_flower_1-1677399236566.png

 

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

lost_flower_4-1677399836453.png

 

Some ideas how this can be fix?

 

Thanks

 

 

20 REPLIES 20
tamerj1
Super User
Super User

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

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

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

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

These are all codes I used in the visual

lost_flower_0-1677407131300.png

 

lost_flower_1-1677407169560.png

 

lost_flower_2-1677407189917.png

 

lost_flower_3-1677407255171.png

 

lost_flower_4-1677407288291.png

 

 

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

do you mean like that?

lost_flower_0-1677409058364.png

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

@lost_flower 

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

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

lost_flower_0-1677414276818.png

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

lost_flower_1-1677414457881.png

 

Is it somehow possible to modify this?

@lost_flower 

Which table / column represents the costomer?

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

lost_flower_2-1677419412434.png

 

 

relationships are:

lost_flower_0-1677419268044.png

 

@lost_flower 

Please try

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

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

lost_flower_0-1677421337644.png

 

@lost_flower 
Please try

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

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

lost_flower_0-1677423828602.png

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

lost_flower_1-1677424199643.png

Is this a relationship issue?

 

@lost_flower 

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

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

lost_flower_0-1677428001753.png

 

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors