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
grantsamborn's avatar
grantsamborn
Solution Sage
4 years ago

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

 

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's avatar
    grantsamborn
    Solution Sage
    4 years ago

    Those calculated columns are in DAX not PowerQuery.

     

    Sorry if I wasn't clear.

    • lea_313's avatar
      lea_313
      Helper I
      4 years ago

      ok great thanks

       

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