Create Measure for multiplication with conditions over 3 tables

[Anonymous]

Hi everyone,

i just started creating a new dashboard for project controlling. Since i haven't used PowerBi for some time i cannot solve this problem: I have 3 tables. First is for entering budegted working days per person. Second table shows the negotiated daily rates per grade. And the last table shows the grade of each Person. Now i want to create a new table (table 4) on my dashboard which shows Name, Days, Revenue. Revenue is calculated by multiplying days with the rate for each person. In Power Bi i can select Name and Days. For Revenue i would create a measure but it does not work. How would you solve this?

Table 1

Name         Month     Days

Person A	January	13,00
Person B	January	0,00
Person C	January	9,00
Person D	January	2,00
Person E	January	11,00
Person F	January	18,00

Table 2

Grade                                   Daily rate

Analyst	1.000,00 €
Consultant	2.000,00 €
Senior Consultant	3.000,00 €
Consulting Manager	4.000,00 €
Principal Consultant	5.000,00 €
Director	6.000,00 €
Partner	7.000,00 €

Table 3

Name          Grade

Person A	Consulting Manager
Person B	Consulting Manager
Person C	Consulting Manager
Person D	Director
Person E	Consultant
Person F	Consultant

Table 4

Name        Days     Revenue

Person A	13,00	52.000
Person B	0,00	0
Person C	9,00	36.000
Person D	2,00	12.000
Person E	11,00	22.000
Person F	18,00	36.000

Thanks and regards

Nitro

[Anonymous]

Table 3 is completely redundant. Move the data from it to Table 1 and then connect 'Table 2'[Grade] to 'Table 1'[Grade] (after moving). 'Table 2' is your dimension that filters 'Table 1'. Then it's easy, isn't it? Revenue = SUMX( 'Table 1', 'Table 1'[Days] * related('Table 2'[Daily rate]) )

[amitchandak]

@Anonymous , you need to have a common dimension and then multiply the measure.

refer how I did date diff across tables : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Greg_Deckler]

@Anonymous - How are you tables connected or are they? Seems like you should be using LOOKUPVALUE I think or RELATED/RELATEDTABLE