Visualizing sum of highest values from multiple categories

[mstd0n]

Hello I am working on a dashboard that displays data scraped from resume files. Because resumes have various formats and spellings I pulled all the date sorted by user file and then use a criteria table to perform some fuzzy matching based on specified categories, and parameters. When candidate resumes are reviewed there is a scoring rubric, and I have added the lowest default value, for example a Bachelor degree is worth a minimum of 5 points but if it is relevant to the job it could be worth more (I call this Draft Score). 

The initial challenge is that a person could have multiple degrees or credentials each with an associated score, and you only want the highest value for a given category. I was able to identify this based on the solution here: Solved: displaying only the highest values - Microsoft Fabric Community 

Here is a view of my current dashboard, I've redacted the information in "source.name" for candidate privacy.

I used the following code to identify the highest item in a category. thanks @Anonymous 

Calculate Highest = 
VAR _tabs =
    SUMMARIZE (
        'Fuzzy Combined',
        'Fuzzy Combined'[Source.Name],
        "@maxqty",
            CALCULATE (
                MAX ( 'Fuzzy Combined'[Draft Score] ),
                ALLEXCEPT ( 'Fuzzy Combined', 'Fuzzy Combined'[Category], 'Fuzzy Combined'[Source.Name] )
            )
    )
RETURN
    IF ( SUM ( 'Fuzzy Combined'[Draft Score] ) = MAXX ( _tabs, [@maxqty] ), 1, 0 )

In the next image you can see the "Calculate Highest" Column is working

My issue is that when I add the measure as a visual filter and attempt to filter my bar chart by "Calculate Highest" is 1, everything is blank. Do I need to create an additional collumn with some sort of if statement in my table first, or is there a simple way to ony pull the draft score for items with a 1 in the Calculate Highest field?

Thank You!

Mstd0n

[v-yueyunzh-msft · Accepted solution]

Hi ,  @mstd0n 

According to your description, you want to get the maximum value in the category of [Source.Name], I checked the case you posted before. We use the previous test data for example:

For your dax code , this is used to filter the visual and it can work in your table visual instead of the bar chart.

For your Dax Code, you need to ensure that [Flower Type] and [Category] and [Components] are placed in your visual objects to take effect. This is because it is judged based on the line.

And if you just want your table visual show the max value , you just need to use this dax code to show the max value group by the [Flower Type] and [Category]:

Measure = var _t = SUMMARIZE('Table','Table'[flower type],'Table'[category] , "max_value" , MAX('Table'[qty]))
return
SUMX(_t , [max_value])

Then we can also put this measure as the bar chart's Y-axis.

The result is as follows:

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[lbendlin]

You're on the right path but ALLEXCEPT is a very blunt tool. You may want to consider using REMOVEFILTERS instead.