cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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

1 ACCEPTED SOLUTION
Resident Rockstar

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 6
Regular Visitor

thank you @Thejeswar very much for the feedback but still strugling

see my table 4

table 5

table 6

do i remove duplicates on the first two tables?

what im looking for is basically something like this

also note the ben amount might differ between each member for example

hoping this makes sense

Resident Rockstar

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

Regular Visitor

@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

Resident Rockstar

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!!

Regular Visitor

@Thejeswar it worked you are the best

Resident Rockstar

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:

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.