Calculate Measure using a relationship

[DamienDhaene]

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

[Anonymous]

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

[Greg_Deckler]

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/547907

Measure = 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.

[DamienDhaene]

@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

[Greg_Deckler · Accepted solution]

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.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...