Forum Discussion
Dynamic Total Measure Dependent on Selected Parameters ?
So I have survey data and for each question/column I have created measure that finds the average. See below for example. My question is, I created a parameter that has each measure so a user is able to select from a slicer which questions they care about and see the scores related to the questions they care about. But I have one issue.
Is it possible to create a measure that dynamically finds the total average of those selected values/questions they select from the slicer ? So if one user only cares about Activities and Cleanliness the Total Average measure would only look at those two measures and so on.
| Category | Activities | Cleanliness | Courtesy | Total Average |
| Property 1 | 4 | 3 | 2 | 3.00 |
| Property 3 | 4 | 4 | 3 | 3.67 |
I have switched my method and am using an unpivoted table now. This method didn't work but granted I didn't provide a copy of the PBIX file so I undertstand I'm limiting the odds of an answer.
10 Replies
Last_Resort_22 Yes, that is certainly possible to do. However, it is difficult to know the exact solution without additional information. If your slicer affects your matrix visual then this should happen automatically. Since that apparently is not occurring then something else must be going on. What is the formula for your Total Average measure?
hi, Last_Resort_22 ,in your case, not necessary to use a parameter. i providing you one senario and hope this can be help:
load your data in power BI, select category and then unpivot other columns.projection your category to have a seperate table.
make this category an relationship one to many to survery table attibute.
create a measure call total average:
Total Average = AVERAGEX(Survey,Survey[Value]), put category table to a slicer. and you can slice what you wantabove solution for your reference.
best regards.
marcoHi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Average score: = VAR _result = AVERAGE ( survey[Value] ) VAR _slicerselect = VALUES ( slicer_question[Question] ) RETURN IF ( HASONEVALUE ( question[Question] ), _result, AVERAGEX ( FILTER ( VALUES ( question[Question] ), question[Question] IN _slicerselect ), CALCULATE ( AVERAGE ( survey[Value] ) ) ) )Is it possible to create a measure with the functionality I mentioned when my model is basically a flat file?
Or do I have to create tables and connect them the way your example shows?
Hi Last_Resort_22 ,
MarcoSparkBI response meets your requirements and addresses the need. Please review it and let us know if any changes are needed.
Thank you for the clear explanation MarcoSparkBI.
Regards,
YugandharHi Last_Resort_22 ,
I wanted to check in regarding your issue. Has it been resolved, or do you need any further information. Let me know if you’d like more details.
Thanks.
Hi Last_Resort_22 ,
Sorry for the delayed reply. Thank you for providing the details of your model. You don’t need to create extra dimension tables to get this functionality. With your flat file structure, you can create a measure that will dynamically average only the questions selected in your slicer.I have switched my method and am using an unpivoted table now. This method didn't work but granted I didn't provide a copy of the PBIX file so I undertstand I'm limiting the odds of an answer.
Thank you for the update. I appreciate your perspective. Switching to an unpivoted table seems reasonable for managing dynamic data.
As you mentioned, it's difficult to give a specific answer without the PBIX file. If you can share a simplified version, a screenshot of your query steps, or the DAX logic, it would help provide more accurate suggestions.
Thanks for your effort.
Hi Last_Resort_22 ,
If you are still working on this or need any help, please share a simplified PBIX file, DAX logic, or the query steps.
I look forward to hearing from you.
Best regards,
Yugandhar
