Some calculations not correct

[Anonymous]

Hi guys, below is the relationship between the tables Daily_Business_Confirmed & Business_Full_Quote, where policy reference is the field that links the tables together.

The first visual shows the number of sales, the middle visual is a count of the number of quotes and the last visual is the click to sale %, which is the following measure = sales count/click count. Some of the percentages are correct some are not, any ideas why?

[MFelix]

Hi @Anonymous ,

Being a one to many relationship set the filter directionality to single side instead of bidirectional.

Also be sure to use the column on the one side as part of your visuals instead of the column from the many side.

Regards,

MFelix

[Anonymous]

Hi it doesn't allow me to set the direction of the relationship to the 'one' in the relationship

[MFelix]

Hi @Anonymous ,

You should change the highlited optionin the relationship table to single:

Regards,

MFelix

[Anonymous]

Thanks, one other question I have is lets say that all the pol references in the Business_Confirmed table are contained in the Business_Full_Table, however what if the Business_Full_Quote table (which is the many in the relationship) contain policy references which are not in the Daily_Business_Confirmed table?

[MFelix]

Hi @Anonymous ,

If you have references that are on one table but not on the other that will give you blank results or incorrect results.

A workaround for this is to create a table with unique values from both tables and then use that table to relate the other two that way you will be abble to get the full results.

Regards,

MFelix

[Anonymous]

How could I do this? 

[MFelix]

Hi  @Anonymous ,

Create a new table using the following DAX code:

Table_Union = DISTINCT(UNION(DISTINCT('Table[Column]), DISTINCT('Table2'[Column])))

The column for table and table 2 should be the ones you want to relate then make a relationship one to many from this table to the other two.

You can also do this in Query editor appending both tables (only the column you need) on a single query and clean duplicates.

Regards,

MFelix

[Anonymous]

Hi, thanks for your help, you have been very helpful, however some calculations are still not correct, these three visuals at the top are all correct.

The counts on these bottom three visuals is correct however not all the percentages in the last visual are correct.

E.g. 

10/112 = 8.93%, the visual has this as 8.85% 

143/569 = 25.13%, the visual has this as 25.22%

But the last set of figures gives the correct percentage:

131/532 = 24.62% - which is correct!

Im wondering if you can help me, I have tried to filted out errors but this has not made a difference.

[MFelix]

Hi @Anonymous ,

Check if the relationships are set with one to many for both tables and the cross filter is set to single.

Can you share a sample of the file? if information is sensitive please send trough private message.

Regards,

MFelix

[amitchandak]

Hope it has been done like

calculate(divide(sum(sales count),sum(click count))

Means divided summarized data.

[Anonymous]

Hi Amit, thank you for help but this doesnt work