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  
CompanyRevenueOwner
Client A1000Geoff
Client B1500Bob
Client C2000Steve
Client A1000Geoff
Client B2000Bob
Client A1500Geoff
Client B1000Bob
Client D3200Geoff

 

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

CompanyRevenueOwnerTier
Client A3500GeoffTier 2 - £3000-£4000
Client B4500BobTier 3 - £4000 - £5000
Client C2000SteveTier 1 - £0 - £2000
Client D3200GeoffTier 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 - £2000Tier 2 - £3000-£4000Tier 3 - £4000 - £5000
Geoff 2 
Bob  1
Steve1  

 

Thanks

Need Help

7 Replies

  • 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.

  • 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 
  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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: