Forum Discussion

JoshEnglish's avatar
JoshEnglish
Icon for Helper I rankHelper I
8 years ago
Solved

Looking up values from a 2-dimensional table

I have a table that lists quotas by quarter for three teams, where the teams are in each row and the quarters are in columns.   I have a query that summarizes sales by team, quarter, and category. ...
  • dramus's avatar
    dramus
    8 years ago

    My first thought is that you need to pivot the Team Quotas table. So that you have three columns (Teams, Quarter and Quota). 

     

    Then create a summary table of the sales that sums the amount.

    Teams Summary = SUMMARIZECOLUMNS('Sales'[Team],'Sales'[Quarter],Sales,"Total Sales",sum(Sales[Amount]))

    I then used a lookupvalue() function to find the matching budget for a Team/Quarter and create a column on the new summary table

    Budget = lookupvalue('Team Quotas'[Quota],'Team Quotas'[Team],[Team],'Team Quotas'[Quarter],[Quarter])

    I can then create a new column for the budget percentage,

    Budget Percent = [Total Sales]/[Budget]

    I'm sure you could combine some of these steps, but I think this makes it clearer what's going on.

dramus's avatar
dramus
Icon for Continued Contributor rankContinued Contributor
8 years ago

My first thought is that you need to pivot the Team Quotas table. So that you have three columns (Teams, Quarter and Quota). 

 

Then create a summary table of the sales that sums the amount.

Teams Summary = SUMMARIZECOLUMNS('Sales'[Team],'Sales'[Quarter],Sales,"Total Sales",sum(Sales[Amount]))

I then used a lookupvalue() function to find the matching budget for a Team/Quarter and create a column on the new summary table

Budget = lookupvalue('Team Quotas'[Quota],'Team Quotas'[Team],[Team],'Team Quotas'[Quarter],[Quarter])

I can then create a new column for the budget percentage,

Budget Percent = [Total Sales]/[Budget]

I'm sure you could combine some of these steps, but I think this makes it clearer what's going on.

  • JoshEnglish's avatar
    JoshEnglish
    Icon for Helper I rankHelper I
    8 years ago

    Thanks. That was the solution.