Forum Discussion
lea_313
4 years agoHelper I
Calculated subtable
Hi I need to analyse some data and get a specific output I am struggeling with: Raw Data Company Revenue Owner Client A 1000 Geoff Client B 1500 Bob Client C 2000 ...
PaulDBrown
4 years agoCommunity Champion
Here is one way.
First, create a new table using "Enter data" in the ribbon under home and type in:
I have also created dimension tables for Owner and Company. The model looks like this:
Next create two measures :
Customers by group =
COUNTROWS (
SUMMARIZE (
FILTER (
FTable,
[Revenue by company] >= SELECTEDVALUE ( Tier[MinThreshold] )
&& [Revenue by company] <= SELECTEDVALUE ( Tier[MaxThreshold] )
),
'Dim Company'[dCompany],
'Dim Owner'[dOwner]
)
)
And for the final matrix:
Count by Group =
SUMX(Tier, [Customers by group])
Create the matrix with the "Dim Owner" field as rows and the Tier table Groups as columns and add the [Count by group] measure to get: