Sum/Total of distinct values

[Atif]

I have multiple values in column 1 and column 2, and I want to divide each distinct value of column 1 with the sum of distinct values of column 2 and plot percentages against values of column 3.

[Atif]

Rather than giving 12.8% against Brand Other1 by dividing 47 from 366, it is showing 100%. All subsequent values are also incorrect.

The formula I have used to get the total of all distinct values of a column is:

Total Distinct Store ID = COUNTX(DISTINCT(VALUES(Table1[Store ID])), CALCULATE(DISTINCT(Table1[Store ID])))

[amitchandak]

@Atif , Not very clear

Try - divide(distinctcount(Table[Column1]),distinctcount(Table[Column2]))

[Atif]

@amitchandak, thanks for the reply. Please check the above snapshot and details.

[amitchandak]

@Atif , Where is denomintor measure

And this should be

Total Distinct Store ID = Distinctcount(Table1[Store ID])

[FarhanAhmed · Accepted solution]

Hello

Can you share sample data for your column1 & column2? are they both on the same table?

Could you please try

Store Distinct = 
Var a = DISTINCTCOUNT(Table1[Column1])
Var b = CALCULATE(DISTINCTCOUNT(Table1[Column2]),ALL(Table1))

REturn DIVIDE(a,b)

[Atif]

The formula worked well. However, if I use a filter, the values are still divided by the Total Distinct Count rather than by the Filtered Distinct Count.

[FarhanAhmed]

Then you need to update your formula to use ALLEXCEPT instead of ALL

and add filter columns in ALLEXCEPT 

Store Distinct = 
Var a = DISTINCTCOUNT(Table1[Column1])
Var b = CALCULATE(DISTINCTCOUNT(Table1[Column2]),ALLEXCEPT(Table1,Table1[Column3],Table1[Column4]))

REturn DIVIDE(a,b)

[Atif]

Thanks, a zillion, @FarhanAhmed!