SUMX using Max

[alfertab]

Hi, everyone,

I've been having troubles with my total POWER BI code and I would like to know if you could help me. My problem is that in the subtotals I am looking to add (using SUMX) the maximums depending on the 2 seasons multiplied by their respective days, while in the normal values ​​I am looking to have only the maximums that were in the season. My current formula is the following:

MAX = IF (HASONEVALUE ( 'Daily Data'[Season]), MAX('Daily Data'[MAX daily]), SUMX('Daily Data',MAX('Daily Data'[MAX daily])))

As it can be observed, in the normal values ​​it does show the maximums depending on the season, however, in the total it shows the value of the maximum of the two seasons (summer) and for some reason it multiplies it by the total days of the month (31). Basically I would look for the total to be something like: (Max in Winter * Winter days)+(Max in Summer * Summer days)

In this table I show an example of what my Power BI table shows. We can see that the total is the maximum of the two seasons for the total number of days (14,043) when it should show the sum (13,935).

Season	Days	MAX
Summer	28	453
Winter	3	417
Total	31	14,043

[vicky_ · Accepted solution]

The reason you get the wrong total days is because the SUMX is iterating over all days in your table, rather than just the two seasons. You can try something like the following: 

MAX = IF (HASONEVALUE ( 
    'Table'[Season]), 
    MAX('Table'[MAX Daily]), 
    SUMX(
        ADDCOLUMNS(
            SUMMARIZE('Table', 'Table'[Season]), 
            "season max", CALCULATE(MAX('Table'[MAX Daily])), 
            "days", CALCULATE(COUNTROWS('Table'))
        ), [season max] * [days]
    ))

[alfertab]

¡Thanks a lot! It was very helpfull 😃