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
KA95
Helper III
Helper III

How to calculate the % of the most common value in a column

Hi there,

 

I have a column called [result_value] that has multiple rows, for example, the most common row is "Art & Design" there are 12 rows with that specific value, I want a % calculated from that.

 

It looks quite simple but I haven't found a way to do it surprisingly. I want to see the total % of the top value in comparison to the rest of the entries, not just specifically "Art & Design", as there might be other values in future that surpass it.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @KA95 
This should work in a cadr visual 

 

Most Common % =
VAR CountAll =
    COUNTROWS ( TableName )
VAR ValuesFrequency =
    ADDCOLUMNS (
        TableName,
        "@Frequency",
            COUNTROWS (
                CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[result_value] ) )
            )
    )
VAR MaxFrequency =
    MAXX ( ValuesFrequency, [@Frequency] )
RETURN
    DIVIDE ( MaxFrequency, CountAll )

 

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

Hi @KA95 
This should work in a cadr visual 

 

Most Common % =
VAR CountAll =
    COUNTROWS ( TableName )
VAR ValuesFrequency =
    ADDCOLUMNS (
        TableName,
        "@Frequency",
            COUNTROWS (
                CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[result_value] ) )
            )
    )
VAR MaxFrequency =
    MAXX ( ValuesFrequency, [@Frequency] )
RETURN
    DIVIDE ( MaxFrequency, CountAll )

 

Hi @tamerj1 

Thanks for the response, I'm not sure why but I'm getting this error when I apply it 

KA95_0-1654441231612.png

 

@KA95 

The last part use CountAll not the table name

Apologies @tamerj1 I'm not sure why I did that... I must have read it wrong, anyway it works now greatly appreciate it. 

@KA95 
Please do not apologies. It was actually my mistake. Please accept my appology and thank you for marking my reply as acceptable solution.

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.

Top Solution Authors