Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nitro
New Member

Create Measure for multiplication with conditions over 3 tables

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 AJanuary13,00
Person BJanuary0,00
Person CJanuary9,00
Person DJanuary2,00
Person EJanuary11,00
Person FJanuary18,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 AConsulting Manager
Person BConsulting Manager
Person CConsulting Manager
Person DDirector
Person EConsultant
Person FConsultant

 

Table 4

Name        Days     Revenue

Person A13,00

52.000

Person B0,000
Person C9,0036.000
Person D2,0012.000
Person E11,0022.000
Person F18,0036.000

 

Thanks and regards

Nitro

3 REPLIES 3
Anonymous
Not applicable

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
Super User
Super User

@Nitro , 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-Differ...

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors