cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Clay82
Frequent Visitor

Most common value from a measure

Hello!

 

Say I have a table with two number columns (A and B).

I have created a measure (not a calculated column) (C) that calculates the differense between the columns (A - B)

A B C
5 3 2
7 2 5
6 4 2
4 2 2


Now I would like to show the most common value (which in this case is 2) in a card viz based on the measure I've created. 

How can I create such a measure?

Thanks!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

 

Picture4.png

 

The most common value: =
VAR add_measureC =
ADDCOLUMNS ( Data, "@measureC", [C:] )
VAR groupby_measureC =
GROUPBY (
add_measureC,
[@measureC],
"@count_rows", SUMX ( CURRENTGROUP (), 1 )
)
VAR max_countrows =
MAXX ( groupby_measureC, [@count_rows] )
RETURN
MAXX ( FILTER ( groupby_measureC, [@count_rows] = max_countrows ), [@measureC] )

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

Simple enough by Excel worksheet formula.

Screenshot 2021-11-16 050115.png

smpa01
Super User
Super User

@Clay82  you can achieve the end goal with thfollowing measure

_diff = SUM(tbl[A])-SUM(tbl[B])

Measure =
VAR _1 =
    ADDCOLUMNS ( tbl, "diff", [_diff] )
VAR _2 =
    ADDCOLUMNS (
        _1,
        "count", COUNTX ( FILTER ( _1, EARLIER ( [diff] ) = [diff] ), [diff] )
    )
VAR _3 =
    ADDCOLUMNS (
        _2,
        "rank", RANKX ( FILTER ( _2, EARLIER ( [count] ) < [count] ), [count],, DESC, DENSE )
    )
RETURN
    MAXX ( FILTER ( _3, [rank] = 1 ), [diff] )

 

smpa01_0-1637004166411.png

 

 

 





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

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

 

Picture4.png

 

The most common value: =
VAR add_measureC =
ADDCOLUMNS ( Data, "@measureC", [C:] )
VAR groupby_measureC =
GROUPBY (
add_measureC,
[@measureC],
"@count_rows", SUMX ( CURRENTGROUP (), 1 )
)
VAR max_countrows =
MAXX ( groupby_measureC, [@count_rows] )
RETURN
MAXX ( FILTER ( groupby_measureC, [@count_rows] = max_countrows ), [@measureC] )

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Wow Jihwan it worked perfectly! Thank you!

Also I need the same for column B (which is in fact a measure based on a calculated column. So another measure to find the most common value in column B. Is it possible for you to help me? 

Hi, 
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

 

Picture2.png

 

The most common value from Column B: =
VAR groupby_columnB =
GROUPBY ( Data, Data[B], "@count_rows", SUMX ( CURRENTGROUP (), 1 ) )
VAR max_countrows =
MAXX ( groupby_columnB, [@count_rows] )
RETURN
MAXX ( FILTER ( groupby_columnB, [@count_rows] = max_countrows ), Data[B] )


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors
Top Kudoed Authors