Forum Discussion
Calculate Measure using a relationship
Hi Community,
I need some help solving the following question.
I have a table containing all contracts a team is responsible for and the % of Sales their commission is calculated on.
| Team | Contract | % |
| A | C.01 | 100% |
| A | C.02 | 100% |
| A | C.03 | 50% |
| A | C.04 | 100% |
| B | C.01 | 50% |
| B | C.05 | 100% |
| B | C.03 | 100% |
| B | C.06 | 100% |
| B | C.07 | 100% |
Next to that I have a contract table containing sales per Contract
| Sales Table | |
| Contract | Sales |
| C.01 | 100 |
| C.02 | 150 |
| C.03 | 70 |
| C.04 | 80 |
| C.05 | 180 |
| C.06 | 120 |
| C.07 | 50 |
Sales over all contracts = 750
I want to create a measure that shows the Commission Sales per Team (when a team is selected) but when nothing is selected doesn't show more then total sales of all contracts = 750.
| Sales x % | ||
| A | C.01 | 100 |
| A | C.02 | 150 |
| A | C.03 | 35 |
| A | C.04 | 80 |
| B | C.01 | 50 |
| B | C.05 | 180 |
| B | C.03 | 70 |
| B | C.06 | 120 |
| B | C.07 | 50 |
Because contracts are not unique in the table above total Sales would be 835 in this case.
Relationship between the two tables is on the contract.
Tx
You are going to have to modify the measure to take Team totals into account. It is basically the same process as grand total lines. Check out my Matrix Measure Triple Threat Rock and Roll.
4 Replies
You can create a couple of measures and you want to use Measure 2 in your table visualization. Based on this Quick Measure, Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Measure = SUMX(FILTER(ALL(Table2),'Table2'[Contract]=MAX('Table1'[Contract])),[Sales])*MAX([%]) Measure 2 = VAR __sum = SUMX('Table2',[Sales]) RETURN IF(HASONEVALUE('Table1'[Team]),[Measure],__sum)Attached PBIX for your reference.
Greg_Deckler Tx for your reply, with the measure you propose I can see that the calculation works on the contract level, however when introducing a slicer on the Team, totals per team do not match the sum of their contracts. When looking at individual teams Totals should match the sum of their contracts, when not selecting any team total should be total of the contracts (without duplication)
In this case Total
Team A = 365
Team B = 470
Grand Total (without slicing on team) 750
You are going to have to modify the measure to take Team totals into account. It is basically the same process as grand total lines. Check out my Matrix Measure Triple Threat Rock and Roll.
Hi DamienDhaene,
You can also take a look at following blog to know how to calculate dax formula on hierarchy total level:
Clever Hierarchy Handling in DAX
Regards,
Xiaoxin Sheng
