Grand Total using the equivalent of SUMIF in DAX

[Marcel_]

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

[Greg_Deckler]

@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.

[Marcel_]

@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!

[PaulOlding · 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.

[Marcel_]

@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