cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

how do I create a static measure, need to use it to divide by filtered total of same column

Hi Everyone,

 

I have a column [Did the student pass Yes/No] and this column has a count total of 100

 

The number of students who passed in 60, therefore to get the pass rate its 60/100

 

However I encounter a problem when I put this in a table with subject matter, i.e. the table shows how many students passed for each subject. e.g Subject |Number of students who passed| Pass rate

                            Maths :    10                                                10/100    (I would like total to remain 100)

                            English:    20                                                 20/100

                            Science:   15                                                 15/100

However instead of the total that I am using to divide remaining 100, it changes to the row total, therefore the calculation is now e.g for maths 10/10 instead of 10/100. please note:  I do not want to use "value as % of total" which is provided with the table as its not suitable for what I am trying to achieve, as I also want to visualise this in other ways.

 

In a nutshell, I would like the denominator as it where, to remain static, as the current measure I have means it isnt.

 

Thank you in advance for all your help

 

P

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: how do I create a static measure, need to use it to divide by filtered total of same column

hi, @Anonymous

After my research, This involves the row context and filter context, you could use ALL() Function in your formula

for example:

Create two measure as below:

Measure = CALCULATE(SUM('Table'[Qty]))

Measure 2 = CALCULATE(SUM('Table'[Qty]),ALL('Table'))

Result:

1.JPG

 

 

here is the reference:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: how do I create a static measure, need to use it to divide by filtered total of same column

hi, @Anonymous

After my research, This involves the row context and filter context, you could use ALL() Function in your formula

for example:

Create two measure as below:

Measure = CALCULATE(SUM('Table'[Qty]))

Measure 2 = CALCULATE(SUM('Table'[Qty]),ALL('Table'))

Result:

1.JPG

 

 

here is the reference:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 257 members 2,714 guests
Please welcome our newest community members: