Calculated subtable

[lea_313]

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	Steve
Client A	1000	Geoff
Client B	2000	Bob
Client A	1500	Geoff
Client B	1000	Bob
Client D	3200	Geoff

this is a sample of the data above

I think I need to sum the revenue by company, and allocate the appropriate 'tier' based on the amount of revenue that company has as a whole

For example the sum total of Client A's revenue in the raw data is £3500 which makes them a Tier 2 client

Company	Revenue	Owner	Tier
Client A	3500	Geoff	Tier 2 - £3000-£4000
Client B	4500	Bob	Tier 3 - £4000 - £5000
Client C	2000	Steve	Tier 1 - £0 - £2000
Client D	3200	Geoff	Tier 2 - £3000-£4000

I then need an output that tells me how many of each tier, each individual owns. Pretty sure I can just do this with a matrix table once the above calculations have been done

	Tier 1 - £0 - £2000	Tier 2 - £3000-£4000	Tier 3 - £4000 - £5000
Geoff		2
Bob			1
Steve	1

Thanks

[PaulDBrown]

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:

[v-yangliu-msft]

Hi  @lea_313 ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table1=
SUMMARIZE(
    'Table',
    'Table'[Company],'Table'[Owner],
    "Revenue",
    CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'),
    'Table'[Company]=EARLIER('Table'[Company])&&'Table'[Owner]=EARLIER('Table'[Owner]))))
return
ADDCOLUMNS(
    _table1,
    "Tier",
    SWITCH(
        TRUE(),
        [Revenue] >=0&&[Revenue]<=2000,"Tier 1 - £0 - £2000",
        [Revenue] >2000&&[Revenue]<=4000,"Tier 2 - £3000-£4000",
        [Revenue] >4000&&[Revenue]<=5000,"Tier 3 - £4000 - £5000"
))

2. Create measure.

Measure =
COUNTX(
    FILTER(ALL('Table 2'),'Table 2'[Owner]=MAX('Table 2'[Owner])&&
    'Table 2'[Tier]=MAX('Table 2'[Tier])),[Owner])

3. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lea_313]

Hiya

Thank you for such a deatiled responce and a file to help too

I seem to be getting the wrong tier allocated to the wrong amount of revenue, see pic below

Table 2 =

var _table1=

SUMMARIZE(

'Mytable',

'Mytable'[CompanyId],'Mytable'[OwnerId],'Mytable'[YearId], 'Mytable'[Stage], 'Mytable'[ProjectCodeId],

"Revenue",

CALCULATE(SUM('Mytable'[Revenue]),FILTER(ALL('Mytable'),

'Mytable'[CompanyId]=EARLIER('Mytable'[CompanyId])&&'Mytable'[OwnerId]=EARLIER('Mytable'[OwnerId])&&'Mytable'[YearId]=EARLIER('Mytable'[YearId])&&'Mytable'[Stage]=EARLIER('Mytable'[Stage])&&'Mytable'[ProjectCodeId]=EARLIER('Mytable'[ProjectCodeId]))))

return

ADDCOLUMNS(

_table1,

"Tier",

SWITCH(

TRUE(),

[Revenue] >0&&[Revenue]<=100000,"Tier 3 - £0 - £100k",

[Revenue] >100000&&[Revenue]<=300000,"Tier 2 £100k - £300k",

[Revenue] >300000&&[Revenue]<=30000000,"Tier 1 £300k+"

))

I should add that I have had to add 'year' and 'stage' for me to be able to filter by these on the visual

Any help would be amazing thank you 

[grantsamborn]

/* Calculated Columns */

TotalRevenue =
VAR _Company = 'DataTable'[Company]
RETURN
CALCULATE(
SUM('DataTable'[Revenue]),
FILTER(
ALL('DataTable'),
'DataTable'[Company] = _Company
)
)

Tier =
IF(
[TotalRevenue] < 2000,
"Tier 1 - 0-2000",
IF(
[TotalRevenue] < 3000,
"Tier 2 - 2000-3000",
IF(
[TotalRevenue] < 4000,
"Tier 3 - 3000-4000",
"Tier 4 - over 4000"
)
)
)

You can use a Matrix with Owner on rows, Tier on columns, and a count of Company in values OR create a calculated table from the results.

[lea_313]

When i replace 'data table' with my table name and then the column in [ ], it just shows various errors

I only have a company ID in this data set. This then links to a company master list that would show their company name.

Thanks for the quick help really greateful

[grantsamborn]

Those calculated columns are in DAX not PowerQuery.

Sorry if I wasn't clear.

[lea_313]

ok great thanks

Will have a look and see how it works, thanks again