Forum Discussion

lea_313's avatar
lea_313
Helper I
4 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
lea_313
Helper I
4 years ago

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