Creating a KPI based on pivot table

[Anonymous]

Hi all,

I want to create a KPI which gives me the total number of 'house_id' where the 'number_of_cars' is greater than 4.

I have created a "pivot visual" in powerbi which looks something like this:

House_id	House_Name	Count_number_of_cars
111222	Tony	10
222111	Sarah	8
111333	Dave	6
123456	Tom	6
234567	Rajan	5
345678	Roshan	15
456789	Wazza	4
567123	Ronaldo	3
234987	Messi	3
987456	House10	2
765432	Akali	1
543216	Ekko	1

The "count_number_of_cars" column is essentially a count of the number of cars each house owns, and the pivot visual is grouping this by "house_id".

I have not created any additional measures or columns here, but i'm finding it hard to create a KPI which tells me the number of records/houses that have more than 4 cars for example as I dont have a single field I can put in the KPI. 

How would I achieve this?

[parry2k]

@Anonymous add a measure to count houses with more than equal to 4 cars:

KPI More than 4 cars = 
SUMX (
   SUMMARIZE ( Table, Table[House_Id], "@Cars Count", COUNTROWS ( Table ) ),
   IF ( [@Cars Count] >= 4, 1 )
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous · Accepted solution]

@parry2k Thanks for suggesting the formula, it didn't work quite as well but i did look into the function and came up with this measure which seems to work:

carcount =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            house,
            [house_id],
            "count", DISTINCTCOUNT ( [number_of_cars] )
        ),
        [count] > 1
    )
)

Is there a way to make this measure interact with my other graphs/tables? For e.g. If this measure tells me the total number of homes with more than 4 cars is 20 homes, then I want my other visuals to filter for these specific 20 homes....if that makes sense?