Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Marcel_
Frequent Visitor

Grand Total using the equivalent of SUMIF in DAX

Hi there, 

 

Happy to meet you all! 

 

I am new to the board and this is my very first message. I have tried to search if there was any answer to my query prior to posting here but couldn't really find what I want. Apologies if this is a doublon of a doublon...

 

Well, I have a Data Model Table that has 3 relevant columns for this exact query.

The Table is called fSeries, it contains a [Quantity] column, a [Country] column and a Racks grouping column called [Racks Grouping] in which there is 3 values : 12-13, 14-15 and 16-17.

I want to create a measure that can SUM the Quantity inside each of the Racks. And when I insert it in my PivotTable and filter it with the Country values, it still shows the SUM (as the Grand Total of what is inside the Racks) of each racks so I can compare the % of country inside the Racks. 

Not too sure if I am clear there but here's what I want to do (data model pivot table): 

 

 

Racks groupingCountry (filtered these 3 for example)Sum of Quantity (implicit measure)Total Quantity in Racks (DAX)
13-14CROATIA4500411290468
 CYPRUS2365211290468
 CZECH REPUBLIC9805011290468
Grand Total   

 

I have tried all sort of measure with Calculate, Filter, All, AllSelected but it is somehow immediately filtered and it shows the exact same amount as 'Sum of Quantity' - which is an implicit PivotTable measure.

 

Again, thanks in advance for any help provided and apologies if the format is not that readable.

 

Cheers, 

Marcel

 

1 ACCEPTED SOLUTION

Yep, you're going down the right path using the CALCULATE function.  

There's a couple of options here

1. Remove the filter from the Country column

Total Quantity in Racks =
CALCULATE(
SUM('Table'[Quantity]),
ALL('Table'[Country])
)
 

2. Remove filters from all columns that aren't Rack Grouping

Total Quantity in Racks =
CALCULATE(
SUM('Table'[Quantity]),
ALLEXCEPT('Table', 'Table'[Racks Grouping])
)
 
These options will both give you the same answer in your sample table.  So, it's just a question of what you want the measure to do if you replaced Country with some other column.

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@Marcel_ Sample data would really help, I am not quite following what the issue is.

 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler My mistake...

 

Sample Data :

 

ProductCountryQuantityRacks Grouping
BingCROATIA500013-14
BangCYPRUS200013-14
ZangCZECH REPUBLIC300013-14
BengCHINA150 00013-14
Rang ITALY120 00013-14
FangCROATIA10 00013-14

 

Output in PivotTable : Let's say I want to filter Racks Grouping to 13-14, and only show 3 countries. I want to drag and drop the [Quantity] column into values which will create the implicit Sum of Quantity measure. However, I want to create a measure that can always showcase the Total Quantity in Racks Grouping, regardless of the Countries I filter. So that I can compare the 2, like % of Quantity the countries are taking in view of the Total Quantity in Racks.

 

Racks GroupingCountrySum of QuantityTotal Quantity in Racks
13-14

CYPRUS

2000190 000
 CROATIA15 000190 000
 CZECH REPUBLIC3000190 000
Grand Total 20 000190 000

 

For now, I have tried many measures. For example : Calculate(Sum(fSales[Quantity]),AllSelected() got me close, as in :

 

Racks GroupingCountrySum of QuantityTotal Quantity in Racks
13-14

CYPRUS

200020 000
 CROATIA15 00020 000
 CZECH REPUBLIC300020 000
Grand Total 20 00020 000

 

Hope this helps!

Yep, you're going down the right path using the CALCULATE function.  

There's a couple of options here

1. Remove the filter from the Country column

Total Quantity in Racks =
CALCULATE(
SUM('Table'[Quantity]),
ALL('Table'[Country])
)
 

2. Remove filters from all columns that aren't Rack Grouping

Total Quantity in Racks =
CALCULATE(
SUM('Table'[Quantity]),
ALLEXCEPT('Table', 'Table'[Racks Grouping])
)
 
These options will both give you the same answer in your sample table.  So, it's just a question of what you want the measure to do if you replaced Country with some other column.

@PaulOlding Hi Paul, 

 

Thank you so much, both solutions worked just fine!

I was in the wrong because I just wouldn't consider the column [Country] to be the issue there.

 

Have a nice day!

 

Best, 

Marcel

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors