Forum Discussion
Multiplication Measure between Unrelated Tables
Hi Everyone! I'm still new to PowerBI and just trying to use measures for the first time.
I have two tables - one with quotas for the full year for different groups and one with the percentages that the quota should be broken down for each quarter. So, for example, the Q1 Quota for Group 1 should be $11,000 and the Q2 Quota for Group 1 should be $15,500. And the same percentages apply to each group.
Seems fairly simple enough, but I've searched around and tried a few different things and can't get it to work. Any help is appreciated!
| Group | FY20 Total | Q1 Quota | Q2 Quota |
| Group 1 | $50,000 | ||
| Group 2 | $10,000 | ||
| Group 3 | $15,000 |
| Quarter | % |
| Q1 | 0.22 |
| Q2 | 0.31 |
| Q3 | 0.24 |
| Q4 | 0.23 |
Anonymous
You can write a measure for each quarter to calucate the amount like so:
Q1 Quota = VAR _Split = CALCULATE ( SELECTEDVALUE ( 'Quota Splits'[%] ), 'Quota Splits'[Quarter] = "Q1" ) RETURN SUMX( Quotas, Quotas[FY20 Total] * _Split )You just cange the quarter referenced in the VAR
4 Replies
- amitchandak
Super User
Anonymous , you need to do it by cross join may be in the new table on as a var table in a formula
like
addcolumns(crossjoin(selectcolumns(Table1, "Group",Table1[Group],"FY20", Table1[FY20]), selectcolumns(Table2,"Quarter" ,Table2[Quarter],"Per", Table2[%])), "Allocated Amount" ,[FY20]*[Per])
FarhanAhmedCommunity Champion
Looking at your data model, I think you need to create 4 measures for each quarter something like this.
Q1 Quota =
Var vQ1 = CALCULATE (SUM(Quarter%), Quarter = "Q1")RETURN
SUM([FY20 Total]) * vQ1- jdbuchanan71
Anonymous
You can write a measure for each quarter to calucate the amount like so:
Q1 Quota = VAR _Split = CALCULATE ( SELECTEDVALUE ( 'Quota Splits'[%] ), 'Quota Splits'[Quarter] = "Q1" ) RETURN SUMX( Quotas, Quotas[FY20 Total] * _Split )You just cange the quarter referenced in the VAR
