Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Atif
Resolver I
Resolver I

Sum/Total of distinct values

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.

1 ACCEPTED SOLUTION
FarhanAhmed
Community Champion
Community Champion

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)







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
Atif
Resolver I
Resolver I

Atif_0-1604659100928.png

 

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
Super User
Super User

@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])

FarhanAhmed
Community Champion
Community Champion

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)







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

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.

FarhanAhmed
Community Champion
Community Champion

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)

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Thanks, a zillion, @FarhanAhmed!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.