Formula to display filtered aggregate value in a card

[Anonymous]

Hello - I have created the filtered  matrix visual below and would like to create a formula to display the total station count (41 in this case) in a card, similar to the Full Market Count card.  Your help is very much appreciated!

[Sean]

@Anonymous

Okay it looks like the station count is a column already containing the count number

So all you need to do is create a MEASURE that sums it and place it in the Card

MEASURE = SUM ( Table[Station Count] )

[Anonymous]

So the 41 stations are filtered down from a total 150.  I am filtering on station count >=3.  The station count is a sum of the stations in each market, that I accomplished via a Matrix visual.  

Station count is a new measure that does a distinct count on the station names that are in the original table.  And I can't use this in the formula you suggest (PBI does not allow me to use a calculated field in the SUM expression.

[Sean]

Okay if its already a Measure why can't you place it in a Card and use either the visual level or page level filters?

[Anonymous]

I don't think you can filter an aggregate value in a card

[Sean]

Okay you have Market Names and Stations Names and the Station Count (Measure) = disctinct count of the Station Names

You basically want to get the Number of Stations in Markets with More than 3 Stations

On the Modeling Tab => click New Table and type this...

Summary Table =
SUMMARIZE (
    TableName,
    TableName[Nielsen Market Name],
    "Number of Stations", [Station Count]
)

 Then create this Measure

Stations in Markets with More than 3 =
CALCULATE (
    SUM ( 'Summary Table'[Number of Stations] ),
    FILTER ( ALL ( 'Summary Table' ), 'Summary Table'[Number of Stations] > 3 )
)

Then this Measure will should give you the answer!

[Anonymous]

So I did not give you all of the information up front (I didn't think it was relative)

The station count by market is dynamic based on my slicer selections.  For example, I am filtering via slicer for client stations and then filtering for markets with more than 3 client stations.  

Your suggestion above provided me with a sum total of all stations, because all markets have more than 3 stations.  It's when I apply the various slicers that the number drops below 3.  

Thank you for your help on this!

[v-haibl-msft]

@Anonymous

How about the result if update the second measure provided by Sean as below.

Stations in Markets with More than 3 =
CALCULATE (
    SUM ( 'Summary Table'[Number of Stations] ),
    FILTER (
        ALLSELECTED ( 'Summary Table' ),
        'Summary Table'[Number of Stations] > 3
    )
)

Best Regards,

Herbert