Data handling help

[krislives42]

Good afternoon all,

Firstly, I would like to apologise if this is something that has already been covered in an alternative post.

I am working with a sharepoint list of audit results. Which is generated using a Power Apps UI. At the moment the powers that be, are analysing said data by copy and pasting it into Excel. With a couple of macros they are getting the results they need. However, I have now been charged with trying to get the Power suite to handle the data without that manual copy and paste.

So I have started to look into Power BI, I have just managed to get the company to allow me to install the Desktop App. I'll layout the current setup briefly, and see if the great brains of this community can point me in the right direction, I will hazard a guess that for the more experienced it will be simply answered.

So the sharepoint list has the basic information, the Name of the Auditor, the name of the person being audited, and then each column for the answer response. We have 'Y', 'N', 'NA' and 'T' as available options. What I need to show is the amount of N's per column, and then displayed alongside the question. as a percentage of the total number of audits in the sharepoint list. 

I.E Did the person pass the Audit? ¦ Total Number of N's ¦ Percentage of N's against total audits conducted.

The issue I am trying to overcome is in excel this is quite simple. CountIF('Sheet Name'!'Column Number' = "N"), drag down remaining rows.

Is this possible within Power BI?  

**Update** So far I have created a new query that drags the data from Sharepoint into Power BI. I have had a play with transforming the data and stripping out the information that is not required. I just do not know how to add that into a custom table with the results I need at this time. 

[v-kkf-msft]

Hi @krislives42 ,

Please create these measures.

Total Number of N's = 
CALCULATE (
    COUNT ( 'Table'[answer] ),
    FILTER ( ALL ( 'Table' ), 'Table'[answer] = "N" )
)

Percentage of N's against total audits conducted = 
DIVIDE ( [Total Number of N's], COUNTX ( ALL ( 'Table' ), 'Table'[answer] ) )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[krislives42]

Hi @v-kkf-msft ,

Thank you very much for taking the time to send that across. Building on that, what if that same example had multiple answer columns. Answer, Answer 1, Answer 2 etc.

Would I need an individual measure for each column, or is there something similar to ForEach, that would then run for me?

Again, huge thanks already for taking the time to help.

[v-kkf-msft · Accepted solution]

Hi @krislives42 ,

If there are multiple answer columns and you don't want to create a separate measure for each column. Then first you need to unpivot all answer columns in Power Query Editor.

Then please create these measures.

Total Number of N's = 
CALCULATE (
    COUNT ( 'Table'[Value] ), 
    VALUES ( 'Table'[Attribute] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Value] = "N" )
)

Percentage of N's against total audits conducted = 
DIVIDE (
    [Total Number of N's],
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Attribute] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[krislives42]

Absolutely brilliant @v-kkf-msft ,

Thank you so much for helping me out on that one. Big thumbs up and accepted as a solution 100%.

Many thanks,