Adding to X Axis

[bdehning]

I had someone provide me a great way to show Top 3 Locations and their Top 3 Injury Cause by first count and then using sum to break ties.  It works great if X axis only has Reporting Location on Top of Injury Cause.  

I would like to add Reporting Location Address to X Axis like below, but from time to time the X Axis breaks.  

Could the measures be adapted to make that work to show the Address?

Placed in Tooltips

Sort measure Count Location Cause =

VAR _FreqByCity = CALCULATE([Count of Total Gross Incurred], FILTER(ALL(InjuryCause[Cause Grouping]), [Top 3 by frequency and Incurred]<4))

VAR _LN = LEN(FORMAT(CALCULATE([Count of Total Gross Incurred], ALL('LossRun'[Reporting Location City])), "text"))

VAR _Pre = _FreqByCity *POWER(10, _LN*2)

VAR _Inc = CALCULATE(RANKX(ALL(LossRun[Reporting Location City]), [Sum of Total Gross Incurred],,ASC,Dense), ALL(InjuryCause[Cause Grouping]))

VAR _Mid = _Inc * POWER(10, _LN)

RETURN

IF(ISBLANK([Count of Total Gross Incurred]), BLANK(), _Pre + _Mid + RANKX(ALL(InjuryCause[Cause Grouping]),[Ref],,ASC,Skip))

Part of Sort Measure

Ref =

VAR _MX =

    MAXX (

        ALL ( InjuryCause[Cause Grouping] ),

        CALCULATE ( SUM ( LossRun[Total Gross Incurred] ) )

    )

VAR _LNGTh =

    LEN ( FORMAT ( INT ( _MX ), "Text" ) ) + 1

RETURN

    COUNT ( LossRun[Total Gross Incurred] ) * POWER ( 20, _LNGTh )

        + SUM ( LossRun[Total Gross Incurred] )

Used as Filter for Top 3 Injury Cause

Top 3 by frequency and Incurred =

IF (

    ISBLANK ( [Sum of Total Gross Incurred] ),

    BLANK (),

    RANKX ( ALL ( InjuryCause[Cause Grouping] ), [Ref],, DESC, SKIP ))

[v-pgoloju]

Hi @bdehning,

Just a gentle reminder  has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @bdehning,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution  it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @bdehning,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @bdehning,

Thank you for reaching out to the Microsoft Fabric Forum Community.

To make your chart sort correctly when showing Reporting Location Address on the X-axis: Create a unique identifier by combining Location City, Address, and Cause Grouping (e.g., City | Address | Cause).

Modify your sort measure to incorporate this combination so it can rank each unique Location-Address-Cause properly. Use this updated sort measure to sort your visual ensuring the visual uses either a hierarchy or a concatenated column (like City - Address) on the X-axis.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & Regards,
Prasanna Kumar

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result in a simple table format.

[bdehning]

Table this request for now. 

Thank You