Average total of each average at a row level if condition

[DonPepe]

Hi,

I try to have in a card the total value present in a table. It used to be easy but here it didn't work as expected. 

I calculate the fill rate wich is the division between the capacity theoric by the real capacity: 

FillRate = 
DIVIDE(
    SUM(SubcoScanning[NbrContainer]),
    SUM(SubcoScanning[Capa Theoric])
)

But some times, errors occurs and I have a fill >100% so I want to filter it.
So I did a flag measure like this :

OverScanning = IF(SUM(SubcoScanning[Capa Theoric])<SUM(SubcoScanning[NbrContainer]),1,0)

It works very well and I have the visual table that I want by filtring with the flag measure :

(first column is the ID)

Now I would like to have a card visual with the value calculated as total (57,7%).

But I can't filter a measure by another measure in the card visual. 

So in fact I need to filter each ID with [Capa Theoric] > [NbrContainer] and apply Sum( [Capa Theoric])/ SUM( [NbrContainer] )

But cannot figure how to pass for the ID level to the global level. 

Do you know how to reach this solution ? 

Thanks in advance for your help,

Don 

[Anonymous · Accepted solution]

Hi  @DonPepe ,

I created some data:

Simulate the case where the same ID is [Capa Theoric] greater than [NbrContainer] and [Capa Theoric] is less than or equal to [NbrContainer]. An Index is added to distinguish

Card charts only show one value that is always aggregated, so we cannot add filters based on this aggregated value.

We can write the filter conditions in the measure, and display the single value that has been filtered by the dax formula on the Card

Here are the steps you can follow：

1. Create measure.

Measure =
DIVIDE(
SUMX(FILTER(ALL('Table'),'Table'[Capa Theoric]>'Table'[NbrContainer]),[NbrContainer]),
SUMX(FILTER(ALL('Table'),'Table'[Capa Theoric]>'Table'[NbrContainer]),[Capa Theoric])
)

2. Result:

Best Regards,

Liu Yang

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

[DataInsights]

@DonPepe,

Try this measure:

FillRate =
CALCULATE (
    DIVIDE (
        SUM ( SubcoScanning[NbrContainer] ),
        SUM ( SubcoScanning[Capa Theoric] )
    ),
    SubcoScanning[Capa Theoric] > SubcoScanning[NbrContainer]
)

[DonPepe]

Hi @DataInsights,

Thanks for your help, it seems that's on a good way because I have now 57.1% but still not the 57.7%. 

I think it's because I can have several line per ID so sometimes 

SubcoScanning[Capa Theoric] > SubcoScanning[NbrContainer]

and sometimes 

SubcoScanning[Capa Theoric] <= SubcoScanning[NbrContainer]

for a same ID. 

Still making some test, I will keep update if it works.

Thanks,

Don