Forum Discussion
Create a measure that performs a different calculation for each value in a field
I want to create a measure that calculates the percentage of rows that contain a specific text value 'x' in column 'A' and a specific text value 'y' in column 'B', out of all rows with x in A and NOT(null) in B. I want the measure to recalculate this for every distinct x value in A. Or rather, recalculate for any selected x value in A.
In my example, I have data from the following question from a questionnaire:
- Question:
Overall, how do you feel about each of these services? - Services [Ask each service they have answered previously that they are aware of]:
'Netflix', 'Disney+', 'HBO', etc... - Responses [for each service]:
'Love it', 'Like it', 'It's okay', 'Dislike it', 'Hate it', [autocode 'Missing data' if not aware at previous question].
I have this data in PBI in the form of an unpivoted table, with each respondent having a 'UNIQUEID' (ignore the 'PID' column), repeated for each service in the 'A2p' column, and their response in the 'Response' column:
I want a single measure to calculate the percentage of people saying they 'Love it' to each service, based only to people responding to that service. I want this for every service, or for every selected service (since I want to put this in a line graph where a chiclet slicer adds/removes lines for each service, and each line is based to its own data.
I.e. I want to calculate the percentage rows that have 'Love it' in Response to a specific service in A2p, out of rows that don't have 'Missing data' in Response to that same service in A2p. I want it to calculate that for every service, or rather for whatever service is selected (e.g. if the measure/visual is filtered down to a single service or split out by multiple services).
The DAX I currently have is:
However I want a single measure to do that latter calculation for all services, so that I can put it in a chiclet slicer and line graph so that I can add/remove lines for each service, where the lines are based to that specific service.
Hope that makes sense 🙂
Cheers
Okay actually I think it is doing the right thing, in the sense that it is doing the calculation for each service and filtering it to that specific service. In fact I don't even need the SELECTEDVALUES arguments, it works even if I just do this:
% Love it = CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), A2p[Response] = "Love it") / CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), A2p[Response] <> "Missing data")
And the chiclet slicer/line graph sorts out splitting it out into each service.
I think the actual problem I'm having is that the numbers I am trying to display are a 3-month rolling average, that uses some DAX in a measure [% Love it 3 month avg] to return the 3-month average (for each selected month) of the [% Love it] measure, and this works for other visuals that aren't doing this chiclet slicer/split out line graph thing, but for this one it's just showing the original calculation [% Love it], not the 3-month rolling average [% Love it 3 month avg].EDIT: Turns out that the date variable that the 3-month avg measure was using was a different one from the date variable that was splitting out the line chart. lol. That's sorted now.
3 Replies
I tried "SELECTEDVALUE(A2p[A2p])" as a filter within the CALCULATE() in the numerator and denominator, like so:
% Love it = CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), SELECTEDVALUE(A2p[A2p]), A2p[Response] = "Love it") / CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), SELECTEDVALUE(A2p[A2p]), A2p[Response] <> "Missing data")
but it gives me a "The True/False expression does not specify a column" error.Oh actually I wrote it like this instead and it resolved that error:
% Love it = CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), A2p[A2p] = SELECTEDVALUE(A2p[A2p]), A2p[Response] = "Love it") / CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), A2p[A2p] = SELECTEDVALUE(A2p[A2p]), A2p[Response] <> "Missing data")However the numbers are still wrong, so I still don't think it's actually doing what it is supposed to be doing.Okay actually I think it is doing the right thing, in the sense that it is doing the calculation for each service and filtering it to that specific service. In fact I don't even need the SELECTEDVALUES arguments, it works even if I just do this:
% Love it = CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), A2p[Response] = "Love it") / CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), A2p[Response] <> "Missing data")
And the chiclet slicer/line graph sorts out splitting it out into each service.
I think the actual problem I'm having is that the numbers I am trying to display are a 3-month rolling average, that uses some DAX in a measure [% Love it 3 month avg] to return the 3-month average (for each selected month) of the [% Love it] measure, and this works for other visuals that aren't doing this chiclet slicer/split out line graph thing, but for this one it's just showing the original calculation [% Love it], not the 3-month rolling average [% Love it 3 month avg].EDIT: Turns out that the date variable that the 3-month avg measure was using was a different one from the date variable that was splitting out the line chart. lol. That's sorted now.