Display count of 3 values from 1 column for 12 data points

[Anonymous]

How's that for a confusing subject line?  😉

I have a table that has 12 specific data points (Attributes) I need to track.  Each data point has 3 possible values (auto, manual or blank) called Method.  I need to produce a table (matrix?) that shows each attribute name (i.e. the column names) down the left-most column, then then count of each of the three possible values in the next 3 columns (see mockup).  I cannot figure out how to get the column names to display (versus their value) nor how to display the 3 columns that count the number of possible values.  Help?

Attributes	Auto	Manual	Blank
Attribute 1	10	5	5
Attribute 2	6	8	6
Attribute 3	5	11	3

[Anonymous]

Hi @Anonymous 

Based on your needs, I have created the following table.

Then you can use the following measure to get the result you want:

Measure = 
VAR _select_attr = SELECTEDVALUE('Table'[Attributes])
VAR _select_Method = SELECTEDVALUE('Table'[Method])
RETURN
CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Attributes]=_select_attr&&'Table'[Method] = _select_Method))

Result:

Best Regards,

Jayleny

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

[Anonymous]

This is what the source data looks like (each column is an attribute).  Each row is a particular Class of computer systems that will be selected via a slicer (whether it's one class or multiple classes combined).

The desired output would look like this (in this mock-up, there are 260 classes but in reality there are more):

I'm grappling with 2 questions:

• How to get the attributes (i.e. the column headers) to be listed vertically as shown in the desired output above?
• How to produce the results of all 12 attributes based on whatever class is selected in the slicer (and do I do that in one formula or 3 different formulas (one for auto, one for manual and one for blank)?  The formula you offered was along the lines of what I'm looking for but not quite.

Does that help or make it more confusing?

[Irwan · Accepted solution]

hello @Anonymous 

it certainly helpfull with your sample data appearance.

1. unpivot your table in power query.

2. replace value for that blank row into Blank value since you want to show them as Blank.

4. create a measure to count

Count = COUNT('Table'[Value])

5. put them in matrix visual.

another note, if you want to group "automated" and "automation" as "auto", you can replace those two value into one value.

Hope this will help.

Thank you.

[Anonymous]

Excellent!  This worked and I was able to adapt the formula to capture variations using SEARCH:

m_Remediation Auto Count = CALCULATE(Countrows('Overall Rollup - Remediation'),SEARCH("auto", 'Overall Rollup - Remediation'[Value],, 0)>0)

Thank you, Irwan!

[Irwan]

hello @Anonymous 

glad it works.

Thank you.

[Anonymous]

Each attribute is a column which contains one of the three values (Attribute 1 method, Attribute 2 method, etc.).  Each attribute has other columns relating to it, the values of which aren't relevant here.  What I'm trying right now is to create 3 different countrows measures (one for each possible method value), that I'm guessing can somehow be used with VAR to account for each attribute????  But basically, I'm nowhere right now.

[Irwan]

@Anonymous 

please share your sample data (remove confidential information).

How does "Attribute 1 method, Attribute 2 method, etc" looks like.

Thank you.

[Irwan]

hello @Anonymous 

i assumed that is your desired outcome, how does your original table look like?

Thank you.