help needed on agrr on count distinct

[lmonincx]

hI, i am trying to put the relative measure from a straight table into a pivot, the pivot has not the checkbox of relative and i need to do it in the expression I have 5 unique relations but one is having two campagnes, in the relative it's calcuted for that specific campagne as 1/6 is 17 % this is good, but if i do a count(distinct Relatie)/count (distinct total relatie) it's getting back with 1/5 20 % I need a formula which gives me the 17 %. Campagne,Sub,Relatie Z,Internet,123 Z,Email,456 Z,Email,678 A,Email,123 A,Internet,375 A,Internet,450 How can i solve this? see attached file with demo data Thanks helping me out. Linda

[#2]

Hi Linda,

Could you please attach the demo data, to work on.

Thanks

[lmonincx]

sorry, attached the file.

thanks!

[#4]

Hi Linda,

I don't know if this is what are you looking for, I hope it could be help you.

Best regards.

[lmonincx]

Many thanks the solution seems so simple

[lmonincx]

Hi,

I tried the solution at my customer but if failed on the results, see attached , i expect tot see the 83,4% of kanaal isstf ( 583/699 where the 699 is the sum of all the uniek relations within the different kanaals) , leaving the expression and turning it into pivot it's using the 583/649 where 649 are the unique realtions in the campagne ( not using the 699).

I need a calculation using the 699 ( sum of unique relations over the kanaals within a campagne)

Any idea how to establish this?

Kind regards, Linda

[#7]

Hi Linda,

What about use a variable for the calculation?

See my attached file.

I hope this help you.

Best regards.

[lmonincx]

Hi Miguel, I am almost there many thanks this realy is a step forward, only thing which remains is that the total line at the end is not counting up to 100% I am not able to turn off the total line, any idea if we can make it display the sum of the values ( 100%)?

Many thanks for your help!

Kind regards, Linda

[#9 · Accepted solution]

Hi Linda,

It has cost me a neuron of my brain, but, after a long time analyzing your data, I found there are some RelatieRespPos nulls and others one repeated in Kanaal aanvraag, so I've changed all again and recalculated all the expressions, check out my example attached.

By the way, I'm not using the variable any more.

I hope this finally help you.

Best regards.

[lmonincx]

Hi Miguel, you are my hero, many thanks for all your time and effort, this is the solution i needed.

Have a nice weekend!

Kind regards,

Linda