The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I've come accross a beauty that i just cannot work out. I've got staff working on jobs and need to show which staff member is generating the most income for us. the data is sitting in2 tables and i need to present it in a Powerbi matrix.
Table 1
Contract# | Job# | Time SOLD | Sales Value |
M309252 | 1 | 1.5 | $450 |
M309252 | 2 | 2.0 | $350 |
M304445 | 1 | 3.0 | $400 |
Table 2
Contract# | Job# | Employee# | Time SPENT | |
M309252 | 1 | 915 | 0.5 | |
M309252 | 2 | 960 | 0.6 | |
M304445 | 1 | 960 | 0.8 | |
M304445 | 1 | 977 | 1.2 |
In the PowerBI Matrix i need a "Total Time Spent", "Total Time SOLD" & "Total Sales Value", all to be calclauted based on the % of "Time SPENT" between technicians on each perticlaur contract/job line.
The end result should look like this in a PowerBI Matrix. I'm sure i'll need some form of measure but have no idea how to do it.
Employee# | Total Time SPENT | Total Time SOLD | Total Sales Value | |
915 | 0.5 | 1.5 | $450 | |
960 | 1.4 | 3.2 | $510 | |
977 | 1.2 | 1.8 | $240 |
Hi @rmorris
create a many to many relationship between the two tables on Contract# then use the following measures
Total Time SPENT =
SUM ( Table2[Time SPET] )
Total Time SOLD =
SUMX (
Table2,
SUMX (
FILTER ( RELATEDTABLE ( Table1 ), Table1[Job#] = Table3[Job#] ),
Table1[Time SOLD]
)
)
Total Sales Value =
SUMX (
Table2,
SUMX (
FILTER ( RELATEDTABLE ( Table1 ), Table1[Job#] = Table3[Job#] ),
Table1[Sales Value]
)
)
In both tables, you need to create a composite key (calculated column: M309252-1) and create a relationship. Then create a summary table.
SummaryTable =
SUMMARIZECOLUMNS('Table2'[Employee],"Total Time Spent", SUM('Table2'[TimeSpent]),"Total Time SOLD",SUM('Table1'[TimeSold]) ,"Total Sales Value",SUM('Table1'[SalesValue])
You can then create measures on top of this table.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
16 | |
15 |
User | Count |
---|---|
94 | |
33 | |
30 | |
24 | |
21 |