cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 grouping Country (filtered these 3 for example) Sum of Quantity (implicit measure) Total Quantity in Racks (DAX) 13-14 CROATIA 45004 11290468 CYPRUS 23652 11290468 CZECH REPUBLIC 98050 11290468 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
Solution Sage

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.
4 REPLIES 4
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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

@Greg_Deckler My mistake...

Sample Data :

 Product Country Quantity Racks Grouping Bing CROATIA 5000 13-14 Bang CYPRUS 2000 13-14 Zang CZECH REPUBLIC 3000 13-14 Beng CHINA 150 000 13-14 Rang ITALY 120 000 13-14 Fang CROATIA 10 000 13-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 Grouping Country Sum of Quantity Total Quantity in Racks 13-14 CYPRUS 2000 190 000 CROATIA 15 000 190 000 CZECH REPUBLIC 3000 190 000 Grand Total 20 000 190 000

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

 Racks Grouping Country Sum of Quantity Total Quantity in Racks 13-14 CYPRUS 2000 20 000 CROATIA 15 000 20 000 CZECH REPUBLIC 3000 20 000 Grand Total 20 000 20 000

Hope this helps!

Solution Sage

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.
Frequent Visitor

@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

Announcements

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

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors