Skip to main content
cancel
Showing results for 
Search instead 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

Reply
NH3_Meijer
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.

 Problem shot 1.PNGProblem shot 2.PNGWhat I want to do In power BI.PNG

 

 

Some Help on this would be Great!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
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.
alanhodgson
Solution Supplier
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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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