cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors