Forum Discussion
How to calculate correctly
i have a data source looking like this
| Member | Member Amount | Product | Medical Aid Price | Benificiary | Ben Amount |
| member 1 | 1000.00 | Medical Aid | 500.00 | Ben 1 | 125.00 |
| member 1 | 1000.00 | Medical Aid | 500.00 | Ben 2 | 125.00 |
| member 1 | 1000.00 | Medical Aid | 500.00 | Ben 3 | 125.00 |
| member 1 | 1000.00 | Medical Aid | 500.00 | Ben 4 | 125.00 |
| member 1 | 1000.00 | Life Insurance | 500.00 | Ben 1 | 125.00 |
| member 1 | 1000.00 | Life Insurance | 500.00 | Ben 2 | 125.00 |
| member 1 | 1000.00 | Life Insurance | 500.00 | Ben 3 | 125.00 |
| member 1 | 1000.00 | Life Insurance | 500.00 | Ben 4 | 125.00 |
| member 2 | 1000.00 | Medical Aid | 500.00 | Ben 1 | 125.00 |
| member 2 | 1000.00 | Medical Aid | 500.00 | Ben 2 | 125.00 |
| member 2 | 1000.00 | Medical Aid | 500.00 | Ben 3 | 125.00 |
| member 2 | 1000.00 | Medical Aid | 500.00 | Ben 4 | 125.00 |
| member 2 | 1000.00 | Life Insurance | 500.00 | Ben 1 | 125.00 |
| member 2 | 1000.00 | Life Insurance | 500.00 | Ben 2 | 125.00 |
| member 2 | 1000.00 | Life Insurance | 500.00 | Ben 3 | 125.00 |
| member 2 | 1000.00 | Life Insurance | 500.00 | Ben 4 | 125.00 |
| member 3 | 1000.00 | Medical Aid | 500.00 | Ben 1 | 125.00 |
| member 3 | 1000.00 | Medical Aid | 500.00 | Ben 2 | 125.00 |
| member 3 | 1000.00 | Medical Aid | 500.00 | Ben 3 | 125.00 |
| member 3 | 1000.00 | Medical Aid | 500.00 | Ben 4 | 125.00 |
| member 3 | 1000.00 | Life Insurance | 500.00 | Ben 1 | 125.00 |
| member 3 | 1000.00 | Life Insurance | 500.00 | Ben 2 | 125.00 |
| member 3 | 1000.00 | Life Insurance | 500.00 | Ben 3 | 125.00 |
| member 3 | 1000.00 | Life Insurance | 500.00 | Ben 4 | 125.00 |
when pulling in to power bi it need to sum the data to look like this it is summing incorrectly any ideas?
the sum of member amount for each member is 1000 not 8000 and the sum for each product is 500 not 2000 and the sum for each benificiary is 125 so the total sum of ben 1-4 for each product must be 500 hope this makes sense if someone can please help
powerbi view
each column must total to 3000 the last column is the detail thats suming to the 2nd and 1st
Hi JATER ,
I missed that there was no directly relation between Product and Beneficiary.
You need to do the below changes for this to work
Table5 = DISTINCT(SELECTCOLUMNS(Table3, "Product", Table3[Product], "Medical Aid Price", Table3[ Medical Aid Price ], "Member", Table3[Member],"Product_Member", CONCATENATE(Table3[Product], Table3[Member])))Table6 = DISTINCT(SELECTCOLUMNS(Table3, "Beneficiary", Table3[Benificiary ], "Ben Amount", Table3[ Ben Amount ], "Product_Member", CONCATENATE(Table3[Product], Table3[Member])))Remove the existing join between Table 5 and Table 6. Instead create a new join between Table 5 and Table 6 using Product_Member column as shown below.
The Output will be as follows
Table 5
Table 6
If this solves your question, Mark it as a Solution. Would love an appreciation with a Kudo!!
6 Replies
Hi JATER ,
This is because of the way your table is. The 1000 Member Amount repeats for each row that is there under Member 1 thereby taking the Member 1 Member Amount to 8,000.
You should think of splitting the tables with one table having only members and member amount, 2nd table having only Product and Medical Aid Price with foreign key reference to Member and 3rd table having Beneficiary and Ben Amount with foreign key reference to Product.
Join 1st and 2nd Table using Member, join 2nd and 3rd table using Product
This way you can get 3000 for all the column.
Tables should be as follows
Output:
Hi JATER ,
I understand that what you have shared is a sample data. But it still works the same even if the amount between Beneficiary changes
Here Table3 is the data that you have shared in this thread.
I created Table 4, Table 5 and Table 6 using the below dax as a Calculated table like shown below
Table4 = DISTINCT(SELECTCOLUMNS(Table3, "Member", Table3[Member], "Member Amount", Table3[ Member Amount ]))Table5 = DISTINCT(SELECTCOLUMNS(Table3, "Product", Table3[Product], "Medical Aid Price", Table3[ Medical Aid Price ], "Member", Table3[Member]))Table6 = DISTINCT(SELECTCOLUMNS(Table3, "Beneficiary", Table3[Benificiary ], "Ben Amount", Table3[ Ben Amount ], "Product", Table3[Product]))The Below is how the tables will look
Table 3 (Same as the data that you have shared)Table 4
Table 5
Table 6
Once the above tables are created, follow the steps in my ealier reply to get the output
Thejeswar thanks almost there, now when all members are selected i get 3000 as total on member amount and medical aid price, but ben amount is 1000 if is select only one member then totals is all three 1000
Thejeswar i cant thank you enough for your help first time im asking here and the assistance is amazing
