Help with Power BI default selections and averages

[svejby]

Hi All,

I seem to have come across this issue a few times. Depending on the data and what I am trying to achieve, I have been able to find work arounds. I just feel like there should be an easier way.

I believe the issue is around averages and date ranges. I have one visual showing the values by date which seems to average correctly. I have another visual showing the values by specifications, this seems to sum the values, but only when there is no selection/interaction with the visuals.

To try and mitigate this I have created a number of measures so that if there is now selection (on the visual), it will show the most recent month, and if there visual is interacted with it will show the value (which is correct).

The issue comes when there is no data for some categories, in the most recent month as it will just sum the data in that category for the whole time period.

Please see below visuals. I have ploted the 3 measures to try and illustrate.

* Pressure average (default) = CALCULATE([Average no. of Pressure Checks (Selection)],filter(pressures,Pressures[is current month]="Yes"))

- this returns the value for the maximum date, for use with default/no selection

* Average no. of Pressure Checks (Selection) = (COUNT(Pressures[Cold inflation pressre (PSI)])/MAX(Pressures[Number of vehicle positions]))/[No. of Pressure Vehicles]

-This returns the value when the date visual has been interacted with.

* Pressure checks per month = if([Pressures Max Selected Date]=[Pressures MAx overall],[Pressure average (default)],[Average no. of Pressure Checks (Selection)])

-This is suppoosed be the measure that i want displayed, that returns the correct value. Is there a different logical test that i can use? eg. one that can identify if a visual has been interacted with?

'Pressure checks per month' shows correctly for fields that have 'Pressure average (default)'. This should be the current month data (June). However, for fields that don't have a 'Pressure average (default)' value, it shows the 'Average no. of pressure checks (selection)' rather than being blank.

When the the date visual is interacted with, it seems to correctly show the averages, rather than summing, as with default/no selection.

Here are the supporting measures:

-No. of Pressure Vehicles = DISTINCTCOUNT(Pressures[Vehicle Serial Number])

-Pressures Max Selected Date = Calculate(MAX(Pressures[Year-Month]))

-Pressures MAx overall = Calculate(MAX(Pressures[Year-Month]),ALL(Pressures))

Essentially what i would like is for the visual to display 'Pressure average (default)' values when there is no interaction with the report (values for 730, R1700 & 12H Grader in yellow). Then if it has been interacted with i would like to show the values in blue in the second image.

Any help on this would be greatly appreciated.

Thanks in advance.

[rubayatyasmin · Accepted solution]

Hi, @svejby 

to show the blanks how about modifying the existing DAX? You are already checking whether it is the current month or not just add if it's blank or not. For example, 

Pressure average (default) =
CALCULATE(
[Average no. of Pressure Checks (Selection)],
FILTER(
Pressures,
Pressures[is current month]="Yes" && NOT(ISBLANK(Pressures[Cold inflation pressre (PSI)]))
)
)

this would give you an idea of how to work further. Though it should work. 

how about using HASONEVALUE in the pressure checks per month measure?

Pressure checks per month =
IF(
HASONEVALUE(Pressures[Year-Month]),
[Average no. of Pressure Checks (Selection)],
[Pressure average (default)]
)

this should show  'Average no. of Pressure Checks (Selection)' if a specific date is selected, and 'Pressure average (default)' if not.

[svejby]

Thanks @rubayatyasmin 

the 'Pressure average (default)' measure didn't seem to change the funtionality, but thats okay it all seems to work.

One slight quirk, the 'Pressure checks per month' measure didnt seem to work for the 730C2 as there was only one entry in March. I tried the Hasonevalue function on a few other columns, just didnt seem to work. As a work around i will just merge the 730C2 with 730 since they are the same thing.

Thanks again.