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
Daniel_Schultz
Regular Visitor

Calculating the top value per summed up category

Hello,

 

I'm looking for a Measure, that returns me the main reason why a certain Item fails in a filtered timespan.

The measure should return a single string, so it can be used in a matrix visual.

 

I created Sample Data to reconstruct the problem:

Daniel_Schultz_0-1669888605161.png

 

The relations look like this: 

Daniel_Schultz_1-1669888738104.png

 

The expected output of the measure "Main Reason for Failure" should return the following given the filter is set to the 01.12.2022:

Daniel_Schultz_2-1669888838446.png

 

My last attempt at this was with MAXX and TOPN. The problem with this is, that it would return Reason 1 for Product A as Main Failure, since it is the largest single amount booked.

But i would need the measure to sum up all bookings in the filtered time span and then return the main failure per product according to the highest sum of amounts over all bookings (6 + 5 > 10 --> Reason 2 for Product A)

If there two reasons have the same sum, it can show either one (like Product C). Its not critical which it shows, it should just not crash the table 🙂

 

Any help or recommandations appreciated!

 

Best Regards

Daniel Schultz

2 REPLIES 2
v-yinliw-msft
Community Support
Community Support

Hi @Daniel_Schultz ,

 

You can try this method:

Create a summarized table first:

Table 2 =
SUMMARIZE (
    SELECTCOLUMNS (
        'Table',
        "Item_Name", 'Table'[Item_Name],
        "RF", 'Table'[Reason of Failure],
        "A",
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                FILTER (
                    'Table',
                    'Table'[Item_Name] = EARLIER ( 'Table'[Item_Name] )
                        && 'Table'[Reason of Failure] = EARLIER ( 'Table'[Reason of Failure] )
                )
            )
    ),
    [A],
    [Item_Name],
    [RF]
)

Then new a measure in the first table:

Main Reason for Failures =
VAR _max =
    CALCULATE (
        MAX ( 'Table 2'[A] ),
        ALLEXCEPT ( 'Table 2', 'Table 2'[Item_Name] )
    )
RETURN
    CALCULATE ( MAX ( 'Table 2'[RF] ), FILTER ( 'Table 2', _max = [A] ) )

The result is:

vyinliwmsft_0-1669947617491.png

Hope this helps you.

Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

Hi @v-yinliw-msft ,

 

Thank you already for the idea with the extra table - it helped.

Sadly it didnt solve the problem completely, since the Main Reason for Failures should also consider active filters on the time and only show the Main Reason for Failures in the filtered time.

 

To explain this better i added some lines (i should have done this beforehand alraedy - sorry for that).

 

This is the current input data:

Daniel_Schultz_0-1670414919262.png

 

Relations stay the same of course and exactly like you did it should be shown in a matrix 🙂

 

These are the expected results with the respective active filters on the date-collumn. Probably its just some addition to your measure that i can't figure out...

 

Daniel_Schultz_1-1670415042196.png

 

Sadly my computer doesnt let me add the pbix-file...

 

Thanks in advance for your help on this!

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.

Top Solution Authors