Counting specific values in a row and combining them

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.

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.