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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ripstaur
Helper III
Helper III

Finding statistical mode filtered by category in DAX

I am working to build a tool that will help a committee evaluate some alternative products. They agree on a set of features they want the product to have, and then rate the products by the features. They also evaluate the criticality of each feature, and use that criticality score to weight the averages of the ratings. I have put together an example here:

RaterFeatureCriticality Ranking
AI5
AII5
AIII4
AIV2
AV2
BI4
BII5
BIII3
BIV2
BV2
CI4
CII5
CIII3
CIV1
CV3
DI4
DII5
DIII3
DIV2
DV3
EI4
EII5
EIII3
EIV1
EV3
FI5
FII3
FIII4
FIV2
FV3

What I want to be able to do is calculate the mode (most frequently occurring value) for each of these ranks, for each feature. I don't know the best way to accomplish this; I had thought I could use a mode calculation to get to it, but can't seem to get DAX  to do it by feature. What I want to end up with is this:

 

RaterFeatureCriticality RankingFeature Mode
AI54
AII55
AIII43
AIV22
AV22
BI44
BII55
BIII33
BIV22
BV22
CI44
CII55
CIII33
CIV12
CV32
DI44
DII55
DIII33
DIV22
DV32
EI44
EII55
EIII33
EIV12
EV32
FI54
FII35
FIII43
FIV22
FV32

 

I did find a DAX formula for mode, in several articles online: 

 

Mode :=
MINX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( Data[Value] ),
            "Frequency", CALCULATE ( COUNT ( Data[Value] ) )
        ),
        [Frequency],
        0
    ),
    Data[Value]
)
 
However, I can't find a way to filter it. What it gives me when I try to build it as a column is a duplicate of the Ranking column. I'm thinking that maybe I'm going about it the wrong way - should I be calculating a table instead of a column, to end upt with something like this:
 
Feature I4
Feature II5
Feature III3
Feature IV2
Feature V2

 

 

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Here is a measure expression that shows one way to do it.  Note that the mode of Feature 5 is 3 (see pic).

mahoneypat_0-1613864834201.png

 

Mode of Ratings =
VAR vSummary =
    ADDCOLUMNS (
        DISTINCT ( Features[Criticality Ranking] ),
        "cCount",
            CALCULATE (
                COUNTROWS ( Features )
            )
    )
RETURN
    CONCATENATEX (
        TOPN (
            1,
            vSummary,
            [cCount], DESC
        ),
        Features[Criticality Ranking],
        ","
    )

 

Note: I used Concatenatex in case you have a tie (it will show both).

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Employee
Employee

Here is a measure expression that shows one way to do it.  Note that the mode of Feature 5 is 3 (see pic).

mahoneypat_0-1613864834201.png

 

Mode of Ratings =
VAR vSummary =
    ADDCOLUMNS (
        DISTINCT ( Features[Criticality Ranking] ),
        "cCount",
            CALCULATE (
                COUNTROWS ( Features )
            )
    )
RETURN
    CONCATENATEX (
        TOPN (
            1,
            vSummary,
            [cCount], DESC
        ),
        Features[Criticality Ranking],
        ","
    )

 

Note: I used Concatenatex in case you have a tie (it will show both).

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.