Forum Discussion
Help with Percentage with conditions - DAX formula question
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!
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
2 Replies
- alanhodgson
Solution Supplier
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
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
