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
swwong1
Helper III
Helper III

% of Grand Total for Any Field

Hi All

 

Referring to my first table, my DAX formula for ALL is: Calculate(Sum(Players), ALL(Table[Sport]))

Can I write a generic ALL statement so that if the user changes the context (instead of sport, use location), the % of Total will still work?

 

I have tried to reference the entire table within the ALL but it doesn't seem to work. Thanks!

 

SportPlayersALL DAX% of Total
Tennis256042%
Basketball206033%
Football156025%
 60  
    
LocationPlayersALL DAX% of Total
ABC126020%
DFC256042%
EFG236038%
 60  
2 REPLIES 2
Cmcmahan
Resident Rockstar
Resident Rockstar

If location is a seperate table, you'll need a different query than if it's in the same table.

 

What does your table structure look like at the moment?

Anonymous
Not applicable

@swwong1 , please send a screenshot of the relationship view.  It will really help us understand the best way to solve this.

 

It sounds like the SUM(Fact_Table[Players]) is your main measure, coming from what I'm calling the Fact_Table.  

I'm going to assume that TableName[Sports] is a dimension table that has a relationship to Fact_Table?

Does [Location] column reside in another dimension table with a different relationship to Fact_Table?

 

If so, you could write the following DAX:

 

All Calc :=
CALCULATE(
	SUM(Fact_Table[Players])
	,ALL(Fact_Table)
)

This will remove any filter on any column in the (expanded) Fact_Table.  This means that any filter on any column that is related to Fact_Table will be removed as well.

 

Again, knowing what your data model looks like (and which columns belong to which tables) will greatly help us solve this problem.

 

 

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.