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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors