cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Measure for Sorting which differs each month

Hi!

I need to build a table?? matrix?? that involves displaying each month in a singular visual.

Each month there is a new ranking of names depending on how much \$ sold.  I can't seem to figure out how to display each Month with changing names. I have tried a matrix, the problem is that the rows need to be in the values field as each month the rank will change.  (There are about 50 or so names each month, this is a small scale example)

Below is an example:

 Month Placing Name \$  Sold Month Placing Name \$  Sold Month Placing Name \$ Solc January 1 Bob 100 February 1 Sally 200 March 1 Joan 187 January 2 Sally 75 February 2 Sam 156 March 2 Travis 178 January 3 John 50 February 3 Bob 134 March 3 Sally 154 January 4 Joan 48 February 4 Joan 123 March 4 Sam 134 January 5 Travis 30 February 5 John 100 March 5 Bob 125 January 6 Sam 10 February 6 Travis 56 March 6 John 110

Is there some sort of DAX that would be able to work with this?

Any input would be appreciated!

3 REPLIES 3
Community Support

Hi All，
Firstly  tamerj1 thank you for your solution!
And @KW123 ,as I understand it, is that how your dataset is structured, and then you want to ask for the sales of the people for each month to be sorted is that right?

Below is the dataset I created modelled after your example data data.

Then we sort by each month and each month's sales.

``````RankPerMonth =
RANKX(
FILTER(
ALL('Table'),
'Table'[Month  ]=MAX('Table'[Month  ])),
CALCULATE(
SUM('Table'[ \$  Sold  ])),,DESC)``````

I hope my answer can solve your problem, if you still have more recent asynchronous needs, you can contact me at any time, I will be the first time to reply after receiving the message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hi @v-xingshen-msft
I'm not in a place to answer on behalf of the requester @KW123. However, I think the requirements are a little bit different than what you have kindly proposed. Attached sample file explains my solution which seems to fully address the requirements.

``````\$Sold New Measure =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[Name], "@Sold", [\$Sold] )
VAR NameCount =
COUNTROWS ( T1 )
VAR CurrentPlacing =
MAX ( Placing[Value] )
VAR Result =
IF (
CurrentPlacing <= NameCount,
MAXX (
TOPN (
1,
TOPN ( CurrentPlacing, T1, [@Sold], DESC, 'Table'[Name], ASC ),
[@Sold], ASC,
'Table'[Name], DESC
),
[@Sold]
)
)
RETURN
Result``````
``````Name Measure =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[Name], "@Sold", [\$Sold] )
VAR NameCount =
COUNTROWS ( T1 )
VAR CurrentPlacing =
MAX ( Placing[Value] )
VAR Result =
IF (
CurrentPlacing <= NameCount,
MAXX (
TOPN (
1,
TOPN ( CurrentPlacing, T1, [@Sold], DESC, 'Table'[Name], ASC ),
[@Sold], ASC,
'Table'[Name], DESC
),
'Table'[Name]
)
)
RETURN
Result``````
``````Month Measure =
IF ( NOT ISBLANK ( [Name Measure] ), MAX ( 'Table'[Month] ) )``````
``````Placing Measure =
IF ( NOT ISBLANK ( [Name Measure] ), MAX ( Placing[Value] ) )``````

Super User

Hi @KW123

Create the following calculated table

Placing = GENERATESERIES ( 1, 100, 1 )

Add a matrix visual. Place 'Table'[Month] in the columns and Placing[Value] in the rows. Don't panic when you see the error 😊

create the flowing measures and place them in the values of the matrix.

Name Measure =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[Name], "@Sold", [\$Sold] )
VAR NameCount =
COUNTROWS ( T1 )
VAR CurrentPlacing =
MAX ( Placing[Value] )
VAR Result =
IF (
CurrentPlacing <= NameCount,
MAXX (
TOPN (
1,
TOPN ( CurrentPlacing, T1, [@Sold], DESC, 'Table'[Name], ASC ),
[@Sold], ASC,
'Table'[Name], DESC
),
'Table'[Name]
)
)
RETURN
Result

\$Sold New Measure =
VAR T1 =
SUMMARIZE ( 'Table', 'Table'[Name], "@Sold", [\$Sold] )
VAR NameCount =
COUNTROWS ( T1 )
VAR CurrentPlacing =
MAX ( Placing[Value] )
VAR Result =
IF (
CurrentPlacing <= NameCount,
MAXX (
TOPN (
1,
TOPN ( CurrentPlacing, T1, [@Sold], DESC, 'Table'[Name], ASC ),
[@Sold], ASC,
'Table'[Name], DESC
),
[@Sold]
)
)
RETURN
Result

Month Measure =
IF ( NOT ISBLANK ( [Name Measure] ), MAX ( 'Table'[Month] ) )

You may also want to add the Placing

Placing Measure =
IF ( NOT ISBLANK ( [Name Measure] ), MAX ( Placing[Value] ) )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors