cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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. 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?

1 ACCEPTED SOLUTION
Continued Contributor

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.

3 REPLIES 3
Continued Contributor

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 I

Thanks. That was the solution.

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors