Counting specific values in a row and combining them

[Presti73]

Hi all,

I am afraid i am thinking too difficult here.
I have a table that consists of an index, yes, no and n/a values.
I need to have (preferably) 3 columns, that count the number of them in a row:

Table 1:

Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | column 7 | column 8 |
1       | Yes            | Yes           | No          | Yes           | No            | n/a        | Yes           | n/a          |
2       | No            | No            | Yes          | n/a           | Yes           | n/a         | n/a          | n/a          |
3       | Yes           | Yes            | Yes         | Yes            | Yes          | Yes         | Yes           | Yes         |
4       | n/a           | No            | No          | n/a            | Yes           | n/a        | No            | Yes          |
5       | No           | No            | No           | No            | No           | No         | No           | No           |

Now i need to have the output of how many times 'yes', 'no' and 'n/a' occurs:

Table 1

Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | column 7 | column 8 | Yes | No | n/a |

1       | Yes            | Yes           | No          | Yes           | No            | n/a        | Yes           | n/a           |4      |2    |2      |
2       | No            | No            | Yes          | n/a           | Yes           | n/a         | n/a          | n/a           |2      |2    |4      |
3       | Yes           | Yes            | Yes         | Yes            | Yes          | Yes         | Yes           | Yes           |8     |0    |0      |
4       | n/a           | No            | No          | n/a            | Yes           | n/a        | No            | Yes           |2     |3    |3      |
5       | No           | No            | No           | No            | No           | No         | No           | No            |0      |8    |0      |

Like i said, i am afraid i am thinking far too difficult, but i cannot seem to make this work.
I have tried a dax formula, but then i can only do 1 count and i get a circular error if i want to run another count on the same columns (different value(s), not related or dependend), i have an unpivoted table, but then i cannot do my calculations correct. Even with measures as addition to the table, do not work, as i need a calculation based on record/row

In my example every calculation i need i can make from these colums, that i can just not make in the unpivoted table (well, i ran out of options there). (counting and percentages, filtered by date, period, person, multiple reports etc.)

I need the numbers of 'yes', 'no' and 'n/a' to calculate how many possible answers (8), how many have been answered ('Yes'+'No'), and how many passed ('Yes' devided by 'Yes'+'No')
I can make calculations if i have Yes and n/a, but ideally i have all 3 of them.
Can anybody please help?

If it works easier, 'yes', 'no' and 'n/a' can be replaced by numbers i.e. 1, 2, 3 or 0, 1, 2

Thanx in advance

[v-xicai · Accepted solution]

Hi @Presti73 ,

>>I need the numbers of 'yes', 'no' and 'n/a' to calculate how many possible answers (8), how many have been answered ('Yes'+'No'), and how many passed ('Yes' devided by 'Yes'+'No')

You may enter into Query Editor, select on the column Column1 - Column8  at the same time, right click to choose option "Unpivot columns", then you can rename the result  columns [Attribute] with "Question" and [Value] with "IsAnswered". Don't forget to click button "Apply & Close" .

Then create measure like DAX below.

CountResult= COUNT(Table1[IsAnswered])    
// how many possible answers (8), display it in Matrix visual.



CountAnswer= CALCULATE(COUNT(Table1[IsAnswered]) ,FILTER(ALLSELECTED(Table1), Table1[IsAnswered]="Yes"&& Table1[IsAnswered]="No" )) 
//how many have been answered ('Yes'+'No')



PercentAnswer= Var d=CALCULATE(COUNT(Table1[IsAnswered]) ,FILTER(ALLSELECTED(Table1), Table1[IsAnswered]="Yes"))                                

Return

DIVIDE(d, [CountAnswer], 0 )

//how many passed ('Yes' devided by 'Yes'+'No')

Best Regards,

Amy 

Community Support Team _ Amy

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

[Presti73 · Accepted solution]

Hi All,

I have tried to implement your sollutions. Invested a great deal of time in the programming, try outs for it and as most of you refer to the unpivoting of the data.

As i tried to explain in question, unpivoting in any construction does not give the needed result. It is not just about counting the numbers of yes/no/na. With the results i need to be aable to do a lot of calculations to create in-depth analyses going beyond a financial report.

So unfortunatly i was not able to solve my puzzle with your, more then appreciated (!!) efforts to help me. Thank you for that.

I have solved it though in another way. It just needed thinking out of the box, and instead of doing the count in PBI, i used SharePoint to pre-calculate. Though the import in PBI is somewhat undesirable as it refuses to import the simple number (i.e. 6 times 'yes' will turn up as 6.000.000.000 in PBI, and 11 times 'no' cannot be calculated as PBI reads it as 1) it did change my way of thinking and made adjustments that benefits the purpose in a lot of ways.

By 'partitioning' the questionaries, i was able to make very detailed reports (yes i make a lot of use of unpivot tables and measures), by having PBI use the seperate lists for in depth analyses and reporting and combine the different lists for a more overall raw quick-view. Though it probably wouldn't be much benefitiairy for financial reports as the amount of list connections would increase, slowing down updates, for my purpose it turned out for the best, utilizing time at his best and increase productivity time.

Again all, thank you for your input and you made me think outside the box, getting creative and solving a near unsolvable puzzle.

[v-xicai · Accepted solution]

Hi @Presti73 ,

>>I need the numbers of 'yes', 'no' and 'n/a' to calculate how many possible answers (8), how many have been answered ('Yes'+'No'), and how many passed ('Yes' devided by 'Yes'+'No')

You may enter into Query Editor, select on the column Column1 - Column8  at the same time, right click to choose option "Unpivot columns", then you can rename the result  columns [Attribute] with "Question" and [Value] with "IsAnswered". Don't forget to click button "Apply & Close" .

Then create measure like DAX below.

CountResult= COUNT(Table1[IsAnswered])    
// how many possible answers (8), display it in Matrix visual.



CountAnswer= CALCULATE(COUNT(Table1[IsAnswered]) ,FILTER(ALLSELECTED(Table1), Table1[IsAnswered]="Yes"&& Table1[IsAnswered]="No" )) 
//how many have been answered ('Yes'+'No')



PercentAnswer= Var d=CALCULATE(COUNT(Table1[IsAnswered]) ,FILTER(ALLSELECTED(Table1), Table1[IsAnswered]="Yes"))                                

Return

DIVIDE(d, [CountAnswer], 0 )

//how many passed ('Yes' devided by 'Yes'+'No')

Best Regards,

Amy 

Community Support Team _ Amy

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

[FrankAT]

Hi @Presti73 ,

give this a try:

Regards FrankAT

[mahoneypat]

The best way to get your counts is to unpivot your data columns.  I put your example data in and generated this query.  To see how to do it, just paste the code below into a blank query in the query editor and click on Advanced Editor (and replace the text there with this code).

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSAgBqFyNRiBSSALlCj4JdPrCyIn6efiFsxkiSQqxSrE61khNMkdD6KYWhmEbAL3WZsDjHGaRLeECIUgPhVo8mCHWKC04v4QghdNf4QIRS6YIeYYldMIKZIkcavFeiKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | column 7 | column 8 |" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | column 7 | column 8 |", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | column 7 | column 8 |", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | column ", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.1", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.2", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.3", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.4", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.5", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.6", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.7", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.8", "Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.9"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | column ", Int64.Type}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.1", type text}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.2", type text}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.3", type text}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.4", type text}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.5", type text}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.6", type text}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.7", type text}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.8", type text}, {"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.9", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | colum.9"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Index | Column 1 | column 2 | column 3 | column 4 | Column 5 | Colum 6 | column ", "Index"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Index"}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
#"Removed Columns1"

I called this table "YesNo" and made a simple measure of Count Measure = COUNTROWS(YesNo).  I then used the new table and that measure in a table and matrix visual to generate the below.  Does this meet your needs?

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat