cancel
Showing results for
Did you mean:

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

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:

 Rater Feature Criticality Ranking A I 5 A II 5 A III 4 A IV 2 A V 2 B I 4 B II 5 B III 3 B IV 2 B V 2 C I 4 C II 5 C III 3 C IV 1 C V 3 D I 4 D II 5 D III 3 D IV 2 D V 3 E I 4 E II 5 E III 3 E IV 1 E V 3 F I 5 F II 3 F III 4 F IV 2 F V 3

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:

 Rater Feature Criticality Ranking Feature Mode A I 5 4 A II 5 5 A III 4 3 A IV 2 2 A V 2 2 B I 4 4 B II 5 5 B III 3 3 B IV 2 2 B V 2 2 C I 4 4 C II 5 5 C III 3 3 C IV 1 2 C V 3 2 D I 4 4 D II 5 5 D III 3 3 D IV 2 2 D V 3 2 E I 4 4 E II 5 5 E III 3 3 E IV 1 2 E V 3 2 F I 5 4 F II 3 5 F III 4 3 F IV 2 2 F V 3 2

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

Mode :=
MINX (
TOPN (
1,
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 I 4 Feature II 5 Feature III 3 Feature IV 2 Feature V 2

1 ACCEPTED SOLUTION
Employee

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

Mode of Ratings =
VAR vSummary =
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!

Employee

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

Mode of Ratings =
VAR vSummary =
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!

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors