SUMIFS in DAX

[mlozano]

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 

[Dhacd]

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]

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]

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

[mlozano]

Hello, I want to build a bar chart, which is filtered by Category and Price Segment column, initially, but it should be possible to filter by any dimension of the data table and the result should be as expected.

without filters

With Channel Detail and price segmnet filters

The result of the sumifs must not be a percentage, it must be in decimals

[v-zhangti]

Hi, @mlozano 

Can you explain how you got the 1.87 at this point? I've filtered and the sum of the Peso SKU Act column should be 1.87 at this point.

Best Regards,

Community Support Team _Charlotte

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

[mlozano]

Hello, thanks for your answer, you mean how the SUMIFS was built from excel to obtain 1.87 as a result.

[amitchandak]

@mlozano , Try measure like

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

[mlozano]

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