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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
malex30
Frequent Visitor

How can I keep multiple ranks and a final dependent ranks values from changing when filtered?

I am creating measures that are averages based on specific columns. 

eg.

Avg1 = AVERAGE('Table'[Rate]) 

**BTW Not sure if this is the best way to implement my averages as opposed to using something else like

(

Avg1 = CALCULATE(AVERAGE('Table'[Rate))
vs

Avg1 = IF(HASONEVALUE('Table'[Item]),CALCULATE(AVERAGE('Table'[Rate)])))

)

 

1. I created my multiple Avg measures. 

2. I created a Rank based off each of those measures

Rank1 = RANKX(CALCULATETABLE(ALL(Table[Item])),'Measures Table'[Avg1],,DESC,Dense)
** In the same way with my Average measure formula. I am not sure if I'm using the current formala for my RANKX. Because I do not want to rank if the value is blank
 (
1Rank =
VAR temp = [Avg1]
RETURN
    IF (
        temp = 0,
        BLANK (),
        RANKX ( ALL (Table[Item]), CALCULATE([Avg1]),,,Dense)
    )
 )
3. I created a final RANKX based on the 6 ranks ( So a Rank average for the rest of the ranks)
Rankings = IF(HASONEVALUE(Table[Item]),RANKX(ALL(Table[Item]),CALCULATE(([Rank1]+[Rank2]+[Rank3]+[Rank4]+[Rank5]+[Rank6])/6),,ASC,Dense))
** Once again I have been down a rabbithole trying to find a solution for this. Which is why you can see I've been trying different formulas. 

 

Below is what I see when I insert a matrix with the avgs and ranks (just 3 of the ranks shown for simplicty)

Based on the averages, the slicer applied and all items shown. THIS IS ALL CORRECT (except the Rank2 and Rank3 are starting at #2 instead of #1. Just another thing I'm trying to figure out...)

ItemsAvg1Rank1Avg2Rank2Avg3Rank3Avg4Rank4Avg5Rank5Avg6Rank6Overall Rank
Item19.14106.9672.96277.90176.6417.5621
Item26.87178.67164.921544.791751.97167.86315
Item310.8637.17104.611154.351258.1997.9247
Item49.06116.9994.20968.48263.9938.4455
Item58.00164.77211.891745.881554.02148.58612
Item610.8916.5764.03657.61768.9828.9772
Item710.3245.8144.691254.371152.29158.9988
Item89.8567.84134.18853.921354.66139.03910
Item99.8957.33115.071656.13955.88129.181011
Item108.78146.3654.831360.40457.82119.52119
Item119.5975.5433.91366.66362.2759.59123
Item128.19157.62124.541059.32560.4379.691310
Item1310.8726.9784.02557.55861.30610.23146
Item149.4788.49153.94458.38658.121010.54159
Item158.98128.06144.14756.051063.20410.851611
Item168.89138.89174.881453.291459.23811.661714
Item179.24910.0018  45.641645.681712.331813

Result when a specific Item is selecting on a given visual graph: INCORRECT Results

ItemsAvg1Rank1Avg2Rank2Avg3Rank3Avg4Rank4Avg5Rank5Avg6Rank6Overall Ranking
Item179.24510.0013  45.641245.681212.331211

EXPECTED Results: Nothing changes when filtered.

ItemsAvg1Rank1Avg2Rank2Avg3Rank3Avg4Rank4Avg5Rank5Avg6Rank6Overall Rank
Item179.24910.0018  45.641645.681712.331813

 

 

The end goal is to have a CARD visual that will show the current OVERALL Rank.

I want the Rank to respect any Slicer filters applied.

BUT keep that rank based on the slicer for each item when filtered by a graph on the page. (Funnel or Line graph). 

Please let me know what else you might need to help me out. 

2 REPLIES 2
v-xiaosun-msft
Community Support
Community Support

Hi @malex30 ,

 

I think that whether you can describe how you calculated the overallrank. In your formula, I think it is the average of total value of ranks. But, what is rank4, rank5, rank6? 

According to the table you gave, I calculate it as followings.

vxiaosunmsft_2-1666691367833.png

But the results are the same.

vxiaosunmsft_0-1666691099085.pngvxiaosunmsft_1-1666691160606.png

Please describle it more clearly in order that we can help you better.

 

Best Regards,
Community Support Team _ xiaosun

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

 

I've updated my original post. 

Also If you notice a couple of my RankX want to start with 2 vs 1. Not sure why it's doing that.. For now I have just been adding a "-1" to the end of the formula. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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