Forum Discussion

mlozano's avatar
mlozano
Icon for Helper III rankHelper III
4 years ago

SUMIFS in DAX

I am trying to create a sumifs in DAX using the CALCULATE function which has as an expression a measure that calculates the ACT volume share percentage of each SKU ID over the total ACT volume. The intention is to calculate the sum of said percentage, including the Month and Category dimensions as a filter argument, but so far I have not obtained the expected result

 

Used the following codes:

 

1. SUMIFS = CALCULATE([peso_sku_act], ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category]))

This option gives me the correct values ​​by category and month, as long as I don't apply any filter, since despite being a measure, every time I apply a filter the values ​​remain static, they are not recalculated.

 

2. SUMIFS = SUMX(Worksheet,CALCULATE([peso_sku_act], ALLEXCEPT(Worksheet, Worksheet[Month], Worksheet[Category])))

In this option I try to force the row context, the total matches the expected result, as long as I don't apply filters, but every time I filter the result is wrong

 

Does anyone know how I can adjust my code to fix the error when building the sumifs?

 

I share Excel that contains the formula and the expected results, in addition to the pbi file

 

Excel Data 

PBI File 

 

 

Need Help

8 Replies

  • Dhacd's avatar
    Dhacd
    Icon for Resolver III rankResolver III
    4 years ago

    Good Day mlozano,

    From the source data, you have shared I have created a small sample data and calculated the sumifs for it screenshot attached below. If your desired output is the same the calculated column formula is attached below.

    Use the below formula for the output.

    sumifs = calculate(Sum('Table'[Peso SKU Act])
                             ,FILTER    ('Table',EARLIER('Table'[Month])='Table'[Month])
                            ,FILTER('Table',EARLIER('Table'[Category])='Table'[Category]))


    If you think this helps you mark this as a solution.
    Thanks and regards,
    Atma.


    • mlozano's avatar
      mlozano
      Icon for Helper III rankHelper III
      4 years ago

      Hi, thanks for your response. Maybe as a calculated column it works, but as a measure it doesn't, it must be a measure, that's why I try to force the row context so that it doesn't always add up to 100%. The calculated column keeps static values, they are not recalculated based on filters applied on the report.

      • Dhacd's avatar
        Dhacd
        Icon for Resolver III rankResolver III
        4 years ago

        Which visual are you trying to create this measure for?
        And what all are the slicers on the page?

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    mlozano , Try measure like

     

    SUMIFS = CALCULATE([peso_sku_act], filter(allselected(Worksheet), Worksheet[Month] = max(Worksheet[Month]) && Worksheet[Category] = max(Worksheet[Category])))

    • mlozano's avatar
      mlozano
      Icon for Helper III rankHelper III
      4 years ago

      Hi, thanks for your response.

      your measure works but they always add up to 1.00 in all the rows and when adding it gives me the sum of all the 1.00, and what I'm looking for is that each row be assigned the sum of peso_sku_act taking into account month and category precisely so that the result It is not 1.00 in all the rows, but the one that corresponds to each category and month.

       

      example

       

      Notice that when I apply the Category filter and the channel detail, the result of my sums is 814.428286 for each row and when I sum the result is 15474.1374. In the excel that I shared you can validate it

       

       

      With your measurement the result is 1.00 for each row and when adding the total it is 19 even if you force the context of the row with SUMX