Help with Percentage with conditions - DAX formula question

[NH3_Meijer]

I have a data sets importing into Power BI from databases. I cannot edit this data sets in excel and need to find a way to get the CORRECT calculated fields in Power BI to finish a project.

What I need is to get a Percentage = ((x_response) / (total responses)).  

example: there are 1877 total responces in 'table 1'[column1], there are 133 distinct responces in 'table 1'[coulmn1]  **(same table and column)**  

133/1877 = 0.06... 6%  I need a way to write a count if function based on unique responces from a column and divide that by total responces of that same column. 

I've attached 3 pictures with my issue.

 

Some Help on this would be Great!

[v-shex-msft · Accepted solution]

Hi @NH3_Meijer,

You can try to use below formula if it works on your side:

Measure:

Dynamic Percent =
var currStore = LASTNONBLANK(Sheet1[Store],[Store])
return
Format(
DIVIDE(COUNTAX(FILTER(ALL(Sheet1),Sheet1[Store]=currStore &&Sheet1[Date of Audit]=MAX([Date of Audit])&&(Sheet1[Audit State]="IS2"||Sheet1[Audit State]="IS3"||Sheet1[Audit State]="NFO"||Sheet1[Audit State]="NAI")),[Audit State]),

COUNTAX(FILTER(ALL(Sheet1),Sheet1[Store]=currStore &&Sheet1[Date of Audit]=MAX([Date of Audit])),[Audit State]),

0),"Percent")

Regards,

Xiaoxin Sheng

[alanhodgson]

Hey @NH3_Meijer,

Not really sure if this is what you are looking for, but you can create a column using:

Column = FORMAT(DISTINCTCOUNT([Audit Status])/COUNT([Audit Status]), "Percent")

This will give you the percentage of distinct records compared to the total records.

If you are looking to have the total responses with no filters, then first make a column like:

Total Responses = CALCULATE(COUNT([Audit Status]), ALL('Table'[Audit Status]))

Hope this helps,

Alan