Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I'm looking at trying to create a custom value from a multiselect slicer but when I try to do things with ALLSELECTED or SELECTEDVALES or VALUES I get weird interactions like yeilding blanks.
The workflow I need:
1. Select two categories in slicer from ~50
2. Get the daily average value of those categories -- needed for the custom calculation below
3. Look at the daily value using those daily average values in the form 1 - (daily avg, cat1/daily avg, cat2)
4. Plot this custom formula value by each day in a PowerBI visual
An Example:
After choosing the category F and P in my slicer my filtered dataset would like this:
Date | Type | Reading | ||
20221026 | F | 1000 | ||
20221026 | P | 4 | ||
20221025 | F | 1100 | ||
20221025 | P | 5 | ||
20221025 | F | 1200 | ||
20221024 | P | 7 | ||
20221024 | F | 1150 | ||
20221023 | P | 11 | ||
20221023 | F | 1000 | ||
20221023 | P | 12 |
Then the daily averages for each type would look something like:
Date | Type | Avg Reading | ||
20221026 | F | 1000 | ||
20221026 | P | 4 | ||
20221025 | F | 1150 | ||
20221025 | P | 5 | ||
20221024 | F | 1150 | ||
20221024 | P | 7 | ||
20221023 | F | 1000 | ||
20221023 | P | 11.5 |
The custom table would something like:
Date | Custom Value | |
20221026 | 0.996 | |
20221025 | 0.995652 | |
20221024 | 0.993913 | |
20221023 | 0.9885 |
And the visual would look something like this:
Thanks for the help!
Thanks again for the help.
To get that custom calculation I would need a calculation that returns for a given day: 1 - (daily avg of first selected value in my slicer)/(daily avg of second selected value in my slicer). The relationship between it and the average column is the day -- the x axis of the visual. So for 2022/10/26: daily avg of F = 1000; daily avg of P = 4; so custom is 1-(4/1000) = 0.996.
To get the daily averages in a DAX way I did this but I'm not sure if helps me get to the final needed custom calculation:
Hi @530 ,
Could you tell me what the definition of your custom calculation is? What is the relationship between it and the "average" column? According to your description, the visual is created by the custom value.
Please provide more description to help you better.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.