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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
aquan
Frequent Visitor

Summarize by multiple columns with context filters applied

Hello

 

I am looking to create a summary table with column names as the first row so that I can use the "Matrix" visualisation.

 

Raw data is this;

IDNumberTHrsCostSubTotal
91027136.6$19,584
91029564.2$21,433
91030647.1$38,579
91031461.2$23,632
91032675.8$36,932
91033689.4$30,172
91034464.8$15,560
91035459$13,819
91036631.8$15,750
9114731$16,009
91148886.6$23,132
91149631.9$42,589
91150317.2$18,277
91151750.5$19,692

 

Summary I am after is this;

 

MetricValue
THrs7347
CostSubTotal$335,160

 

Doing a calculated table using "summarize" like below is fine, but totals do not change in the context of the filters against the raw data table.

 

calculated Table = UNION(
SUMMARIZE(RawData,"Metric","THrs","Value",SUM(RawData[THrs]))
,
SUMMARIZE(RawData,"Metric","CostSubTotal","Value",SUM(RawData[CostSubTotal]))
)
 
So my question is, how do I show a summary table like the above that will change with the filters.
 
Thanks
Phil
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Calculated tables, once calculated, never change.

 

What you need is something different, mate. You need a parameter table beside the one with your data. The param table, Metrics, would be something like this:

 

Metric Name

--------------

THrs

CostSubTotal

 

and it would be disconnected from the data table.

 

Then you have to create 2 base measures:

_THrs = SUM( T[THrs] )

-- and 

_CostSubTotal = SUM( T[CostSubTotal] )

Then create a composite measure:

[Metric] =
var __selectedMetric = SELECTEDVALUE( Metrics['Metric Name'] )
var __metricValue =
-- you can fomat the measures under SWITCH with FORMAT SWITCH( __selectedMetric, "THrs", [_THrs], "CostSubTotal", [_CostSubTotal] ) return __metricValue

This metric will return a value if there's a selected metric in the current context.

 

If you now put your Metrics dimension in a table and then drop the [Metric] measure on it, you'll get what you want and you'll be able to slice by other attributes as well.

 

Best

Darek

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Calculated tables, once calculated, never change.

 

What you need is something different, mate. You need a parameter table beside the one with your data. The param table, Metrics, would be something like this:

 

Metric Name

--------------

THrs

CostSubTotal

 

and it would be disconnected from the data table.

 

Then you have to create 2 base measures:

_THrs = SUM( T[THrs] )

-- and 

_CostSubTotal = SUM( T[CostSubTotal] )

Then create a composite measure:

[Metric] =
var __selectedMetric = SELECTEDVALUE( Metrics['Metric Name'] )
var __metricValue =
-- you can fomat the measures under SWITCH with FORMAT SWITCH( __selectedMetric, "THrs", [_THrs], "CostSubTotal", [_CostSubTotal] ) return __metricValue

This metric will return a value if there's a selected metric in the current context.

 

If you now put your Metrics dimension in a table and then drop the [Metric] measure on it, you'll get what you want and you'll be able to slice by other attributes as well.

 

Best

Darek

Thankyou very much! Worked perfectly.

 

I didn't expect the simplest way to be that fiddly!..

 

To go a step further in the automation of this summary table (a nice to have);

I guess the first part (creating the Metrics table) could be automated by "unpivoting" the data table so you have the column names as row items; could could also add an index (auto-number) column.

 

Which leaves the 2nd part;

Instead of hard-coding the column names into the measure, you could create measures, _1, _2, _3 to a fixed number (unsure if this could be dynamic and extend according to the max index).

Then I'm not sure if the SWITCH statement in the composite measure could be automated using the index's?

 

Or could you see another way to automate it? 

Anonymous
Not applicable

In DAX there's no way to create a string dynamically and execute it (like in SQL, for example). And this feature is the basis for the dynamic behavior you're talking about. When you create measures, you have to hard-code things. A measure cannot anticipate something before it's in there in the model. It just has to be there.

 

Sorry, you're a bit out of lack here. Maybe one day...

 

Best

Darek

Ok - thankyou for your help.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.