cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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

8 REPLIES 8
Resolver III

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.

Helper II

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.

Resolver III

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

Helper II

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

Community Support

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.

Helper II

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

Super User

@mlozano , Try measure like

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

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors