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