Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mlozano
Helper II
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

 

Excel Data 

PBI File 

 

 

8 REPLIES 8
Dhacd
Resolver III
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.

Dhacd_0-1654579050209.png

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.


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.

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

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

mlozano_0-1654641932655.png

 

With Channel Detail and price segmnet filters

 

mlozano_1-1654642045641.png

mlozano_2-1654642086634.png

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

 

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.

vzhangti_0-1654765367640.png

 

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.

 

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

amitchandak
Super User
Super User

@mlozano , Try measure like

 

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

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

 

mlozano_1-1654608484916.png

 

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

 

mlozano_2-1654608646277.png

 

 

 

 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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