Get the maximum sum of sales for all dates

[dpombal]

Hi all ,I am facing problems with a quite basic Dax formula, in fact looks like a bug ( I am using Power bi desktop Version: 2.129.1229.0 64-bit (May 2024)).

Download sample PBIX link https://1drv.ms/u/s!Am7buNMZi-gwhJ5EZUHgExUn_dUgeA?e=j1qr6U

My datatset is a basic SalesT table with 4 columns: a sales person (Firstname), SessionType (needs to be filtered), Date and SalesAmount.

SalesT table

What I am looking for is the maximum sum of sales grouped by date for all dates, so I need to retrieve for all dates the sum of sales amount and finally select the maximum value. Session types must be filtered to exclude AllDay and Prepare values as shown in below image.

When FirstName and Date are not filtered the value for the measure SalesMaxDate should be 300.

Image 01

no filters

However when filtering Date Day 7th of May, the value of the measure changes. Correct value should be 300 but measure shows 290

Image02

filtered day 7 of Month May

Same behaviour when filtering Date=Day 7th of May and  FirstName=Paul. Correct value should be 200 but measure shows 175

Image03

Filtered Name and Date

I tried with 2 sample measures with no luck to Remove Date field filter and summarize to get the value I am looking for.

SalesMaxDatev1 =

MAXX (

CALCULATETABLE ( VALUES(SalesT[Date]),

                 REMOVEFILTERS ( SalesT[Date] )

                 ),

CALCULATE ( sum(SalesT[SalesAmount]))

)

Measure  version 2

SalesMaxDatev2 = MAXX (


CALCULATETABLE (


SUMMARIZE (SalesT,

                         SalesT[Date] ,

                         "SUMSALESBYDAY", sum(SalesT[SalesAmount]) )

,

REMOVEFILTERS ( SalesT[Date]  )


) , [SUMSALESBYDAY])

Regards

[Anonymous · Accepted solution]

Hi,@dpombal 

Regarding the issue you raised, my solution is as follows:

The reason why the maximum value is not 300 is because of the influence of the slicer "session type". When you select the two dates, the slicer "session type" also follows the time slicer and filters only the value whose session type is night, as shown in the figure below. Thus the maximum value is returned to 290.

I offer you two solutions:

1.The first method, to manage the relationship between visual objects, disconnects the relationship between the date slicer and the two maximum visual objects, as shown below:

The following documents are relevant to help you:

Change how visuals interact in a report - Power BI | Microsoft Learn

2.The second method:

Create a new table with a date column to ensure that the date slicer does not affect the "session type" of the slicer:

Table = SELECTCOLUMNS('SalesT',"Date",'SalesT'[Date])

Use the date column of the new table as the new slicer:

Here are the measure for replacing the bar chart visual object:

Measure = 
VAR cc=VALUES('Table'[Date])
RETURN CALCULATE(SUM(SalesT[SalesAmount]),FILTER(SalesT,'SalesT'[Date] IN cc))

3.Here are the final results:

Please find the attached pbix relevant to the case.

Method 1 is in Page 1, Method 2 is in page2

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dpombal]

With the SessionType influence you give me the tip I was looking for,

I will try to do an extra option 3 adding to my DAX formula with a fixed filter excluding  SessionType ("AllDay","Prepare").

SUMMARIZE(     FILTER('SalesT',                      

NOT ( 'SalesT'[SessionType ) IN { "AllDay","Prepare"}

....

                                          )

Regards