Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello,
I have Table A set up as follows:
I then have a separate Table B for just one title, Locums, because it is pulling from a separate data source. (Linked on Location) This is set up a little differently:
I have a measure Total FTE, which is basically TableA.TotalFTE + TableB.TotalFTE. But, I'd like to break down the Total FTES by title in various visuals. I'm getting confused on how to do this, as TAble A is Regular MD and Regular CRNA, and Table B is Locum MD and Locum MD. For instance, I'd like to put it on a stacked bar chart by Location to show the % of each title.
Hi @aashton
You can create a new table.
Table 2 = SUMMARIZE(TableA,[Title])
Then create a measure in tableA
Measure = SWITCH(TRUE(),MAX('Table 2'[Title])="MD",CALCULATE(SUM(TableA[FTE])+SUM(TableB[MD FTE]),TableA[Title]="MD",TableB[Location]=MAX(TableA[Location])),MAX('Table 2'[Title])="CRNA",CALCULATE(SUM(TableA[FTE])+SUM(TableB[CRNA FTE]),TableA[Title]="CRNA",TableB[Location]=MAX(TableA[Location])),SUM(TableA[FTE])+SUM(TableB[MD FTE])+SUM(TableB[CRNA FTE]))
Put the "location" field in table a to x-axis and "title" field in table 2 to legnd, and put the measure to y-axis.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I changed the format of Table B, I thought it might be easier if set-up like this:
I then wanted to create a column with an If statement, IF(TableA.Title = "MD", "Regular MD", IF(TableB.Title = "Locum MD", "Locum MD"....etc...But it's not letting me pick fields from different tables
Hi @aashton
Based on the change of table B, you can refer to the following example.
Create two tables
Table = UNION(SUMMARIZE(TableA,[Title]),SUMMARIZE(TableB,[Title]))
Table 2 = var a=VALUES(TableB[Location])
var b=SUMMARIZE(FILTER(TableA,[Location] in a=FALSE()),[Location])
return UNION(a,b)
Then create a measure
Measure = CALCULATE(SUM(TableA[FTE])+SUM(TableB[FTE]),TableA[Location]=MAX('Table 2'[Location]),TableB[Location]=MAX('Table 2'[Location]),TableA[Title]=MAX('Table'[Title]),TableB[Title]=MAX('Table'[Title]))
Put the location field of table 2 to x-axis, put the title of tale to legend, put the measure to y-axis
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xinruzhu-msft ...thanks for your help! But the titles in Table A and and TAble B should be different, not combined. Table A are REgular MD and CRNA and Table B are Locum MD and CRNA. And there are more titles than just MD and CRNA, I just used those examples.
Thanks but I am getting the same totals for each location:
Hi @aashton
The "Title"measure you have provided should be
Title = CALCULATE(SUM('CR HCM Monthly Data'[FTE Status])+SUM('Ringo Locum Leased'[Total Ringo FTE]),'CR HCM Monthly Data'[Worker Region]=MAX('Table 2'[Biz_Structure Region]),'Ringo Locum Leased'[Biz_Structure Region]=MAX('Table 2'[Biz_Structure Region]),'CR HCM Monthly Data'[Complex Title]=MAX('Table'[Complex Title]),'Ringo Locum Leased'[Title]=MAX('Table'[Complex Title]))
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
212 | |
89 | |
76 | |
66 | |
60 |