Clustered Column Visual

[bdehning]

I have the following visual which uses filter  It is set less than 4

Top 3 by frequency  =  

IF (

    ISBLANK ( [Count of Total Gross Incurred] ),

    BLANK (),

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

and also uses measure Top N where Top is 3

Sort measure Count Tenure Cause =

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

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

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

VAR _Inc = CALCULATE(RANKX(ALLSELECTED(LossRun[LOE 2023]), [Count of Total Gross Incurred],,ASC,Dense), ALLSELECTED(InjuryCause[Cause Grouping]))

VAR _Mid = _Inc * POWER(10, _LN)

RETURN

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

How do I eliminate the blank column showing for 1 yr.? 

I assume because it leaves space for the Strain Blue column the others have ones have but for 1 yr, strain is not in top 3.     

[v-zhouwen-msft]

Hi @bdehning ,

Regarding your question, I think there may be a problem with the ranking, I created the measure and calculated columns and they both show 1.

You can try modifying it in this format

If I understand you wrong, can you share your .pbix file without sensitive data?

[bdehning]

Hard to share file, which part did you modify with your Rank=?  Was it the Top 3 By Frequency or the Sort Measure?   

[v-zhouwen-msft]

Hi @bdehning ,

I think we should change it here.

Assuming that the column 'Cause Grouping' has the values 'No Data','<1yr','yr', and you have cleared the filtering capability of the field using the ALL function, the 'Ref values should all be the same, for sorting all are 1

[bdehning]

I was trying this earlier and trying to figure out What is Sheet1 and Sheet1[Type] and then Col2.   I Assume Sheet1 is Injury Cause and Type is Cause Grouping.  Not sure what Col2 is for me?   Its Either Count of Total Gross Incurred or LossRun[LOE 2023]?

[v-zhouwen-msft]

Sorry for the confusion. These are my hypothetical data, 'Sheet1' is 'InjuryCause', 'Type' is 'Cause Grouping', and 'col2' is the value of 'Ref'. Can you share some simple data?

[bdehning]

Here is what I am trying but when I use EARLIER('InjuryCause'[Cause Grouping])) 'InjuryCause'[CauseGrouping]  is in red?   Is not not like EARLIER?  Refers to an earlier row context which doesn't exist?

Top 3 by frequency =

IF (

    ISBLANK ( [Count of Total Gross Incurred] ),

    BLANK (),

    RANKX (FILTER('InjuryCause', 'InjuryCause'[Cause Grouping] = EARLIER('InjuryCause'[Cause Grouping])),LossRun[Count of Total Gross Incurred],,DESC,Skip)

[v-zhouwen-msft]

Hi @bdehning ,

This function requires a row-context computing environment .Please attempts to create a calculated column, not a measure.

[bdehning]

I tried to use 'InjuryCause'[Cause Grouping] after EARLIER and it stays red.   Is not 'Sheet1'[Type] not the same on both sides of the =?

[v-zhouwen-msft]

Hi @bdehning ,

I am very sorry for taking you in the wrong direction, let's ignore the ranking issue for a moment, the reason for the blank columns is probably due to the use of the legend.

In the table visual object, only the top three figures are shown, but in the clustered bar chart, blank columns are shown, which should be due to the use of a legend.

Please follow these steps:

Use the following DAX expression to create a measure

Measure 2 = 
VAR _a = SELECTEDVALUE('Injury Cause'[Cause])
RETURN SWITCH(TRUE(),
_a = "STF","Red",
_a = "Strain","Blue",
_a = "Struckby","Green",
_a = "Caught between","Yellow")

Setting the color

[bdehning]

I realized the Legend was the issue as my table of date is fine.   Unfortunately there are 250 Injury Causes possible, so setting the 4 with color in your measure will not solve this .  

[bdehning · Accepted solution]

I solved the issue by adding both Cause and LOE to my X-Axis.