Forum Discussion
Helper IIMathematical operation from non joined table
Hi,
I have two table Table 1 and Table 2.
Table 1 contain Course Group and status and and thier respective sum
| Course Group | Confirmed | Conditional Firm | Offers to be accepted | Pending Applications | Insurance |
| First Degree | 142 | 458 | 32 | 35 | 423 |
| First Degree with Fnd Yr | 108 | 130 | 17 | 19 | 169 |
| Other Undergraduate | 4 | 6 | 2 | 3 | 6 |
| Primary ITE | 7 | 111 | 2 | 13 | 72 |
| Secondary ITE | 2 | 23 | 1 | 2 | 5 |
| Primary PGCE | 9 | 73 | 6 | 48 | 0 |
| Secondary PGCE | 7 | 73 | 5 | 92 | 0 |
| Postgraduate | 15 | 17 | 4 | 9 | 0 |
Table 2 contain Target grouped for Course Group
| Group | Target |
| First Degree | 709 |
| First Degree with Fnd Yr | 220 |
| Other Undergraduate | 111 |
| Postgraduate | 224 |
| Primary ITE | 120 |
| Primary PGCE | 158 |
| Secondary ITE | 30 |
| Secondary PGCE | 154 |
Now i want to get 0.9 *((Sum of Confirmed)+(Sum of conditional firm) - Target
this should give output as
| First Degree | -164 |
| First Degree with Fnd Yr | -8 |
| Other Undergraduate | -6 |
| Primary ITE | -14 |
| Secondary ITE | -8 |
| Primary PGCE | -79 |
| Secondary PGCE | -77 |
| Postgraduate | -31 |
I am finding difficult to join the two table as i am getting many to many relationship and is there any way i can complete my requirements.
I'm not sure why you have M:M relationship, as i don't see any duplicates in your dataset. However, to fix it, you can consider creating a Table which contains all unique Group values, then using that to create 1:M relationships
- this is the group table (I created this through Power Query)
- and then I can get 1:M relationships since each value in the group table is unique.
The other way to do it would be through DAX:
In this example, i'm assuming that Table 1 has all the Course Groups listed:Output = var summaryTable = ADDCOLUMNS(VALUES('Table'[Course Group]), "confirmed and conditional", CALCULATE(SUM('Table'[Confirmed]) + SUM('Table'[Conditional Firm])) * 0.9, "target", CALCULATE(SUM('Table (2)'[Target]), TREATAS(VALUES('Table'[Course Group]), 'Table (2)'[Group])) ) RETURN SUMX(summaryTable, [confirmed and conditional] - [target])note - i can't match your output exactly because i don't know where that missing bracket in your formula goes, but you can play around with the above. Hope that helps.
2 Replies
vicky_Super User
I'm not sure why you have M:M relationship, as i don't see any duplicates in your dataset. However, to fix it, you can consider creating a Table which contains all unique Group values, then using that to create 1:M relationships
- this is the group table (I created this through Power Query)
- and then I can get 1:M relationships since each value in the group table is unique.
The other way to do it would be through DAX:
In this example, i'm assuming that Table 1 has all the Course Groups listed:Output = var summaryTable = ADDCOLUMNS(VALUES('Table'[Course Group]), "confirmed and conditional", CALCULATE(SUM('Table'[Confirmed]) + SUM('Table'[Conditional Firm])) * 0.9, "target", CALCULATE(SUM('Table (2)'[Target]), TREATAS(VALUES('Table'[Course Group]), 'Table (2)'[Group])) ) RETURN SUMX(summaryTable, [confirmed and conditional] - [target])note - i can't match your output exactly because i don't know where that missing bracket in your formula goes, but you can play around with the above. Hope that helps.
Shravan133To achieve your requirements in Power BI, you can create a new table that combines and calculates the necessary values from Table 1 and Table 2. Here's a step-by-step guide to do this:
- Ensure Your Tables Have the Proper Relationships
- Table 1 should have the Course Group and the different statuses.
- Table 2 should have the Course Group and the Target.
- Create Relationships
Ensure there's a relationship between Table 1 and Table 2 on the Course Group column. If you face a many-to-many relationship issue, consider creating a bridge table with unique Course Group values.
- Create a Calculated Table or Measure for the Calculation
You can use DAX to create a new calculated table or measure that will perform the calculation. For simplicity, let's create a measure.
Steps in Power BI
- Create a Relationship
Ensure both tables are related on the Course Group column.
- Create a Measure
Create a new measure to perform the calculation:
Result =
VAR Sum_Confirmed = SUM('Table 1'[Confirmed])
VAR Sum_ConditionalFirm = SUM('Table 1'[Conditional Firm])
VAR TargetValue = SUM('Table 2'[Target])
RETURN
0.9 * (Sum_Confirmed + Sum_ConditionalFirm) - TargetValue
This measure calculates the desired value for each Course Group.
- Create a Table to Display Results
Use the Result measure in a new table visual to display the results:
- Add the Course Group column from Table 1 or Table 2 (since they are related, it doesn't matter).
- Add the Result measure to the Values.
Example DAX Measure
Result =
SUMX(
VALUES('Table 1'[Course Group]),
0.9 * (
CALCULATE(SUM('Table 1'[Confirmed])) +
CALCULATE(SUM('Table 1'[Conditional Firm]))
) -
CALCULATE(SUM('Table 2'[Target]))
)
Expected Output
Course Group
Result
First Degree
-164
First Degree with Fnd Yr
-8
Other Undergraduate
-6
Primary ITE
-14
Secondary ITE
-8
Primary PGCE
-79
Secondary PGCE
-77
Postgraduate
-31
This approach ensures you calculate the required values based on the relationships between Table 1 and Table 2. The use of the SUMX function iterates over each Course Group, performing the calculation and summing the results.