Forum Discussion
Helper ILooking up values from a 2-dimensional table
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.
Helper II 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. The Column headers are Team, Quarter, Category, Amount.
I would like to calculate the percentages of each category and quarter against the quota.
I cannot find a formula to put into a column that would do this lookup?
Is this even possible?