Measure to calculate average
Okay, so I have this dataset.
What I want to do is checking the average number of attempts a student has to take to get to a 6 or higher. So far I came up with:
Students = CALCULATE(DISTINCTCOUNT(StudentResults[studnr]);StudentResults[result]>=6)
Which is how many students passed each class. In SQL, I'd use a Group By and a Having Max. How can I implement this in a measure in Power BI?
powerbi dax measure
asked Nov 24 '18 at 19:52
MoiMoi
145
add a comment |
Okay, so I have this dataset.
What I want to do is checking the average number of attempts a student has to take to get to a 6 or higher. So far I came up with:
Students = CALCULATE(DISTINCTCOUNT(StudentResults[studnr]);StudentResults[result]>=6)
Which is how many students passed each class. In SQL, I'd use a Group By and a Having Max. How can I implement this in a measure in Power BI?
powerbi dax measure
asked Nov 24 '18 at 19:52
MoiMoi
145
add a comment |
Okay, so I have this dataset.
What I want to do is checking the average number of attempts a student has to take to get to a 6 or higher. So far I came up with:
Students = CALCULATE(DISTINCTCOUNT(StudentResults[studnr]);StudentResults[result]>=6)
Which is how many students passed each class. In SQL, I'd use a Group By and a Having Max. How can I implement this in a measure in Power BI?
powerbi dax measure
asked Nov 24 '18 at 19:52
MoiMoi
145
Okay, so I have this dataset.
What I want to do is checking the average number of attempts a student has to take to get to a 6 or higher. So far I came up with:
Students = CALCULATE(DISTINCTCOUNT(StudentResults[studnr]);StudentResults[result]>=6)
Which is how many students passed each class. In SQL, I'd use a Group By and a Having Max. How can I implement this in a measure in Power BI?
powerbi dax measure
powerbi dax measure
asked Nov 24 '18 at 19:52
MoiMoi
145
asked Nov 24 '18 at 19:52
MoiMoi
145
edited Nov 25 '18 at 12:10
Moi
asked Nov 24 '18 at 19:52
MoiMoi
145
asked Nov 24 '18 at 19:52
MoiMoi
145
asked Nov 24 '18 at 19:52
MoiMoi
145
145
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
To calculate the average number of attempts a student needs to achieve a successful result (>=6) for a course, we need to divide the total number of attempts by the number of succesful attempts.
When counting the total number of attempts, we need to discard the attempts that have not yet led to a successful result, because you cannot count the number of attempts it took to achieve a successfull result if this succesfull result has not been achieved yet.
So the pseudo formula is AVG #Attempts = (all attempts - #No Success Yet) / #Successful Attempts
These are the three base measures:
All Attempts = COUNTROWS ( 'StudenResults' )
#Succesfull Attempts = COUNTROWS ( FILTER ('StudenResults', 'StudenResults'[result] >= 6))
#no succes yet =
SUMX (
'StudenResults',
CALCULATE (
IF ( MAX ( 'StudenResults'[result] ) < 6, 1, 0 ),
ALLEXCEPT ( 'StudenResults', StudenResults[studnr], StudenResults[course] )
)
)
This measure calculates the requested average:
AVG #Attempts = DIVIDE([All Attempts] - [#no succes yet], [#Succesfull Attempts], BLANK ())
A matrix visual, with [course] on Rows and the measures on Values, would look like this:
If you want the average for each student, just put [studnr] on Rows in stead of [course]
answered Nov 25 '18 at 8:10
Marco VosMarco Vos
1,998149
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53461829%2fmeasure-to-calculate-average%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
To calculate the average number of attempts a student needs to achieve a successful result (>=6) for a course, we need to divide the total number of attempts by the number of succesful attempts.
When counting the total number of attempts, we need to discard the attempts that have not yet led to a successful result, because you cannot count the number of attempts it took to achieve a successfull result if this succesfull result has not been achieved yet.
So the pseudo formula is AVG #Attempts = (all attempts - #No Success Yet) / #Successful Attempts
These are the three base measures:
All Attempts = COUNTROWS ( 'StudenResults' )
#Succesfull Attempts = COUNTROWS ( FILTER ('StudenResults', 'StudenResults'[result] >= 6))
#no succes yet =
SUMX (
'StudenResults',
CALCULATE (
IF ( MAX ( 'StudenResults'[result] ) < 6, 1, 0 ),
ALLEXCEPT ( 'StudenResults', StudenResults[studnr], StudenResults[course] )
)
)
This measure calculates the requested average:
AVG #Attempts = DIVIDE([All Attempts] - [#no succes yet], [#Succesfull Attempts], BLANK ())
A matrix visual, with [course] on Rows and the measures on Values, would look like this:
If you want the average for each student, just put [studnr] on Rows in stead of [course]
answered Nov 25 '18 at 8:10
Marco VosMarco Vos
1,998149
add a comment |
To calculate the average number of attempts a student needs to achieve a successful result (>=6) for a course, we need to divide the total number of attempts by the number of succesful attempts.
When counting the total number of attempts, we need to discard the attempts that have not yet led to a successful result, because you cannot count the number of attempts it took to achieve a successfull result if this succesfull result has not been achieved yet.
So the pseudo formula is AVG #Attempts = (all attempts - #No Success Yet) / #Successful Attempts
These are the three base measures:
All Attempts = COUNTROWS ( 'StudenResults' )
#Succesfull Attempts = COUNTROWS ( FILTER ('StudenResults', 'StudenResults'[result] >= 6))
#no succes yet =
SUMX (
'StudenResults',
CALCULATE (
IF ( MAX ( 'StudenResults'[result] ) < 6, 1, 0 ),
ALLEXCEPT ( 'StudenResults', StudenResults[studnr], StudenResults[course] )
)
)
This measure calculates the requested average:
AVG #Attempts = DIVIDE([All Attempts] - [#no succes yet], [#Succesfull Attempts], BLANK ())
A matrix visual, with [course] on Rows and the measures on Values, would look like this:
If you want the average for each student, just put [studnr] on Rows in stead of [course]
answered Nov 25 '18 at 8:10
Marco VosMarco Vos
1,998149
add a comment |
To calculate the average number of attempts a student needs to achieve a successful result (>=6) for a course, we need to divide the total number of attempts by the number of succesful attempts.
When counting the total number of attempts, we need to discard the attempts that have not yet led to a successful result, because you cannot count the number of attempts it took to achieve a successfull result if this succesfull result has not been achieved yet.
So the pseudo formula is AVG #Attempts = (all attempts - #No Success Yet) / #Successful Attempts
These are the three base measures:
All Attempts = COUNTROWS ( 'StudenResults' )
#Succesfull Attempts = COUNTROWS ( FILTER ('StudenResults', 'StudenResults'[result] >= 6))
#no succes yet =
SUMX (
'StudenResults',
CALCULATE (
IF ( MAX ( 'StudenResults'[result] ) < 6, 1, 0 ),
ALLEXCEPT ( 'StudenResults', StudenResults[studnr], StudenResults[course] )
)
)
This measure calculates the requested average:
AVG #Attempts = DIVIDE([All Attempts] - [#no succes yet], [#Succesfull Attempts], BLANK ())
A matrix visual, with [course] on Rows and the measures on Values, would look like this:
If you want the average for each student, just put [studnr] on Rows in stead of [course]
answered Nov 25 '18 at 8:10
Marco VosMarco Vos
1,998149
To calculate the average number of attempts a student needs to achieve a successful result (>=6) for a course, we need to divide the total number of attempts by the number of succesful attempts.
When counting the total number of attempts, we need to discard the attempts that have not yet led to a successful result, because you cannot count the number of attempts it took to achieve a successfull result if this succesfull result has not been achieved yet.
So the pseudo formula is AVG #Attempts = (all attempts - #No Success Yet) / #Successful Attempts
These are the three base measures:
All Attempts = COUNTROWS ( 'StudenResults' )
#Succesfull Attempts = COUNTROWS ( FILTER ('StudenResults', 'StudenResults'[result] >= 6))
#no succes yet =
SUMX (
'StudenResults',
CALCULATE (
IF ( MAX ( 'StudenResults'[result] ) < 6, 1, 0 ),
ALLEXCEPT ( 'StudenResults', StudenResults[studnr], StudenResults[course] )
)
)
This measure calculates the requested average:
AVG #Attempts = DIVIDE([All Attempts] - [#no succes yet], [#Succesfull Attempts], BLANK ())
A matrix visual, with [course] on Rows and the measures on Values, would look like this:
If you want the average for each student, just put [studnr] on Rows in stead of [course]
answered Nov 25 '18 at 8:10
Marco VosMarco Vos
1,998149
edited Nov 25 '18 at 10:47
answered Nov 25 '18 at 8:10
Marco VosMarco Vos
1,998149
answered Nov 25 '18 at 8:10
Marco VosMarco Vos
1,998149
answered Nov 25 '18 at 8:10
Marco VosMarco Vos
1,998149
1,998149
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53461829%2fmeasure-to-calculate-average%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
