Summarize by multiple columns with context filters applied

[aquan]

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;

IDNumber	THrs	CostSubTotal
91027	136.6	$19,584
91029	564.2	$21,433
91030	647.1	$38,579
91031	461.2	$23,632
91032	675.8	$36,932
91033	689.4	$30,172
91034	464.8	$15,560
91035	459	$13,819
91036	631.8	$15,750
91147	31	$16,009
91148	886.6	$23,132
91149	631.9	$42,589
91150	317.2	$18,277
91151	750.5	$19,692

Summary I am after is this;

Metric	Value
THrs	7347
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

[Anonymous · Accepted solution]

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

[aquan]

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]

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

[aquan]

Ok - thankyou for your help.