cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Filtered Card using Sum of Counts

I want to create a card which can be filtered for component count and component type using the example below and return the # of vehicles with that combination. In the card I want to change the count from 1 to 5 and allow for 3 different component types. In the example below a card filtered to gauge and 2 count should return 2 vehicles. A card filtered to switch and 1 count should return 3 vehicles.

 

VehicleComponent TypeComponent Part Number
XXAAAAGaugeRed
XXAAAAGaugeYellow
XXAAAASwitchBlue
XXBBBBGaugeGreen
XXBBBBGaugeSilver
XXBBBBSwitchBlack
XXCCCCSwitchBlue
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Filtered Card using Sum of Counts

@BI_User_312 look at the attached solution, you can choose components, # of components and it will give vehicle count.

 

There are many tables in the attached solution, just focused on cars and components table and what if parameter.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

11 REPLIES 11
Highlighted
Super User IV
Super User IV

Re: Filtered Card using Sum of Counts

@BI_User_312 sorry it is a bit confusing what you are actually looking for and what is the business logic, maybe it is me, could you elaborate bit more.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper I
Helper I

Re: Filtered Card using Sum of Counts

I have a data set which has rows that represent part numbers and the type of components it is. If a vehicle has 5 gauges it will have 5 rows. If it has 3 switches it will have 3 switch rows. I need to get the switch count, gauge count, etc. I understand this but I need a card which I will be able to show for example the total number of vehicles  which have exactly 5 gauges. I will do this for gauges and switches in counts from 1 to 5. I tried to show this in my example table. I was thinking I could create one card  that I could set the filter for desired count and for desired component type. The result is one number showing total number of vehicles with that configuration. Hope that makes sense. @parry2k Thank you

Highlighted
Super User IV
Super User IV

Re: Filtered Card using Sum of Counts

@BI_User_312 I think I know what you are looking for, but to clarify, you will filter on switches or gauges and then another filter like a number, and then you want to show KPI, how many cars are with filter switch or gear and with that number of records.

 

in other words, you want to answer the following:

 

give me a list of cars count with 5 switches

give me a list of cars count with 2 switches 

give me a list of cars count with 10 gauges

and so on...

 

 

 

Am I correct? and it is easily doable once I know this is what you are looking for.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Microsoft
Microsoft

Re: Filtered Card using Sum of Counts

@BI_User_312 

I see your question but I think there is something missing in the condition. There is some confusion, because if you select "Gauge" and  “2” count, you can count to the first 2 rows of the sample. The count of Vehicle is only XXAAAA so it is 1. 

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

Highlighted
Helper I
Helper I

Re: Filtered Card using Sum of Counts

@parry2k Yes, I believe you understand.

 

@V-pazhen-msft Look again. As parry says, I would set a filter on the KPI for gauges and a count of 2. It would count XXAAA + XXBBBB and results in a visual of 2. Alternatively, I would set a filter on the KPI for switches and count of 1 and it would return XXAAAA+XXBBB+XXCCCC and result in a visual of 3. It is summing all of the vehicles with the configuration I have set up in the filter.

 

Thank you both for your help.

Highlighted
Super User IV
Super User IV

Re: Filtered Card using Sum of Counts

@BI_User_312 look at the attached solution, you can choose components, # of components and it will give vehicle count.

 

There are many tables in the attached solution, just focused on cars and components table and what if parameter.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper I
Helper I

Re: Filtered Card using Sum of Counts

@parry2k Please see attached picture. These vehicles should have a count of two not one. With component count at 1 the table should be blank. With component count at two then  XXAAAA and XXBBB should only be on two rows with a count of two. It doesn't matter that the gauges are different part number. I don't need distinct count, only general count of number if gauges/switches.

 

Gauges.JPG

Highlighted
Helper I
Helper I

Re: Filtered Card using Sum of Counts

@parry2k Additional detail. I think it is double counting. In the attached picture where the filter is set to 2 gauges the number shown should read 1 as it should only count this one vehicle that I am filtered to. The gauge position to the left is set to show gauges that are filtered to a count of 1 so in this example it should be  blank. This vehicle has two gauges so only gauge position 2 should read 1.

 

Gauges.JPG

Highlighted
Helper I
Helper I

Re: Filtered Card using Sum of Counts

Also, there is no change in value when componet count is 1 or 0. I  need both.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors