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

     

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

 

  • alfertab's avatar
    alfertab
    Helper I
    2 years ago

    ¡Thanks a lot! It was very helpfull 😃