Forum Discussion

alfertab's avatar
alfertab
Helper I
2 years ago
Solved

SUMX using Max

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).
 
SeasonDaysMAX
Summer28453
Winter3417
Total3114,043
Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

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

     

2 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

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