Filter a table based on bar chart measure %

[hmbedford]

Hi All

I have a list of shipments between various ports (Lanes), and for each shipment in the lane whether it was dispatched on time from origin port and comments for any shipments that were late leaving

I have created measures that calculates total % of on time shipments.

Now i have a bar chart that shows against the lane axis, the % total of shipped on time. The customer wants to see next to this the associated comments for any lane that has a KPI <95% on time shipping, i have added a table with the visual level filter where the on time measure is <95%

The problem is (As i understand it) that the data in the table visual is at shipment level, not lane, so the KPI measure for on time is calculated per shipment and the percentages do not align to the lane 

How do i filter the table based on the lane % 

[v-cherch-msft]

Hi @hmbedford

It seems you may create a measure and use it in visual level filter for the table. Here is the article for your reference.If it is not your case, please share some data sample and expected output. You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Cherie

[hmbedford]

Hi @v-cherch-msft

Thanks for your help, i tried to followthe link but couldnt get to the result i want. I have re-created the report simply to remove any customer references and have some screen shots below (OneDrive sharing externally is disabled in my organisation)

Here's the dummy data set:

I have create the following calculation for the KPI Measure:

% On Time = 1 - (CALCULATE ( COUNT ( Shipments[Target Met/Missed] ) , Shipments[Target Met/Missed] = "Missed" )  / CALCULATE ( COUNTROWS ( Shipments ) ) )

The bar chart shows a summary by lane the overall percentage:

Immingham- Cuxhaven is < 95 % and would like to show the comments in a table associated to this data only.

Somehow we need the table to only show the data we need, but the measure includes all

Thanks, Hayden

[v-cherch-msft · Accepted solution]

Hi @hmbedford

You may try to use below measure and drag it to visual level filter<95%.

% On Time =
1
    - (
        CALCULATE (
            COUNT ( Shipments[Target Met/Missed] ),
            Shipments[Target Met/Missed] = "Missed",
            ALL ( Shipments[Comments] )
        )
            / CALCULATE ( COUNTROWS ( Shipments ), ALL ( Shipments[Comments] ) )
    )

Regards,

Cherie

[hmbedford]

Hi @v-cherch-msft

Brilliant, it worked a treat. Thank you so much.

Thanks

Hayden