6 Replies Latest reply: Nov 13, 2017 6:20 AM by Ruan Haese RSS

    Count total distinct

    Ruan Haese

      Hi everyone

       

      Im working on some data that looks like this:

      DimA, DimB, Amount

      Blue, 1,  100

      Blue, 2,  100

      Blue, 3,  100

      Green, 1, 100

      Green, 2, 100

       

      Count(Distinct DimB) results in barchart as

      Blue, 3

      Green, 2

       

      And the formulae COUNT(DISTINCT TOTAL DimB) results in 3

       

      If I try and get the right relative value for each dimension, so that

      Blue = 3 / 5 = 60%

      Green = 2 / 5 = 40%

       

      But the formulae COUNT(DISTINCT DimB) / COUNT(DISTINCT TOTAL DimB)  results in =>

      Blue 3/3 = 100%

      Green 2/3 = 66%

       

      I.e. the 3 / 5 should refer to the total sum of the rows.

       

      In a bar chart how can I get the right relative value so that its based on the sum of the rows and not the count distinct?

       

      Thank you