Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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)
Proud to be a Super User!
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])))
@Atif , Not very clear
Try - divide(distinctcount(Table[Column1]),distinctcount(Table[Column2]))
@amitchandak, thanks for the reply. Please check the above snapshot and details.
@Atif , Where is denomintor measure
And this should be
Total Distinct Store ID = Distinctcount(Table1[Store ID])
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)
Proud to be a Super User!
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.
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)
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |