cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper IV

## How to bar chart data based on filtered data

I have a data table that is in basically the below format, but it has over 200 objects and time readings down the a 15 minute interval.

I need to calculate 2 values based on a week of the data.

I first need to calculate the Average reading between 3AM and 6AM on Sundays.

I am doing this by using the following formula

=Calculate(average('Table'[Number]),'Date'[DayOfWeekNumber]=7,'Table'[Hour]>=4, 'Table'[Hour]<=6)

Which is working correctly for 1 selected but not working correctly with more than one selected.

I also need to calculate the maximum Number for the selected week for each Object selected, including multiple selected objects.

Here is the formula that I have so far.  It works properly when I have 1 Object selected.  But it does not work if I select 2 or more objects.

=MAXX(ALLSELECTED('table'),'table'[number])

With Multiple objects selected the maximum number should be the Sum of the Maximum of the objects selected.  The Maximum number can occur at different times of the week for different objects.

This is what type of formula I need to get to work in DAX.

=Max(object1) + Max(Object2)...

I then need to figure out the Percentage of the as shown below.

=Divide(Average, maximum) * 100

I will use this ration to chart all the Objects on a Horizontal Bar Chart.

I have tried to chart the above formulas in red to get the Percentage using the formula in blue.
but when I try and chart it on a bar chart it shows it has a percentage of 33% on the Bar Chart but if I use a card and look at the calculated Percentage it is actually 59%.

As I move down the chart another object shows 3% but the Percentage Calculation shows 51%.

I hope I have edited this and made it clearer on what is going on.

 DateHour Number Object 8/27/2020 1:00 66 Object1 8/27/2020 1:00 12 Object2 8/27/2020 3:00 67 Object1 8/27/2020 3:00 6.01 Object2 8/27/2020 5:00 101 Object1 8/27/2020 5:00 8 Object2 8/27/2020 7:00 61 Object1 8/27/2020 7:00 10.01 Object2 8/27/2020 9:00 60 Object1 8/27/2020 9:00 43 Object2 8/27/2020 11:00 111 Object1 8/27/2020 11:00 10 Object2 8/27/2020 13:00 78 Object1 8/27/2020 13:00 13 Object2 8/27/2020 15:00 80 Object1 8/27/2020 15:00 7.01 Object2 8/27/2020 17:00 28 Object1 8/27/2020 17:00 7.01 Object2 8/27/2020 19:00 60 Object1 8/27/2020 19:00 9 Object2 8/27/2020 21:00 58 Object1 8/27/2020 21:00 27 Object2 8/27/2020 23:00 58 Object1 8/27/2020 23:00 34 Object2 8/28/2020 1:00 63 Object1 8/28/2020 1:00 13 Object2 8/28/2020 3:00 61 Object1 8/28/2020 3:00 8 Object2 8/28/2020 5:00 115 Object1 8/28/2020 5:00 6.01 Object2 8/28/2020 7:00 60 Object1 8/28/2020 7:00 9.01 Object2 8/28/2020 9:00 62 Object1 8/28/2020 9:00 46 Object2 8/28/2020 11:00 99 Object1 8/28/2020 11:00 27 Object2 8/28/2020 13:00 68 Object1 8/28/2020 13:00 17 Object2 8/28/2020 15:00 77 Object1 8/28/2020 15:00 8 Object2 8/28/2020 17:00 32 Object1 8/28/2020 17:00 9 Object2 8/28/2020 19:00 30.01 Object1 8/28/2020 19:00 8 Object2 8/28/2020 21:00 0.04 Object1 8/28/2020 21:00 6.01 Object2 8/28/2020 23:00 29 Object1 8/28/2020 23:00 7.01 Object2
1 ACCEPTED SOLUTION
Community Support

HI @moltra,

It sounds like a common requirement about applies multiple aggregate functions on measure formula, perhaps you can take a look at the following link if it meets your requirements:

DAX Create measure with multiple aggregations over 2 columns

Sample formula:

``````_Average =
VAR summary =
SUMMARIZE (
ALLSELECTED ( Table ),
[Category],
"AVG",
CALCULATE (
AVERAGE ( 'Table'[Number] ),
'Date'[DayOfWeekNumber] = 7,
'Table'[Hour] >= 4,
'Table'[Hour] <= 6
)
)
RETURN
AVERAGEX ( summary, [AVG] )

Total_maximum=
VAR summary =
SUMMARIZE ( ALLSELECTED ( table ), [Category], "MAX", MAX ( 'table'[number] ) )
RETURN
SUMX ( summary, [MAX] )
``````

Notice: replace above 'category' part with your visual axis and legend fields.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Community Support

HI @moltra,

It sounds like a common requirement about applies multiple aggregate functions on measure formula, perhaps you can take a look at the following link if it meets your requirements:

DAX Create measure with multiple aggregations over 2 columns

Sample formula:

``````_Average =
VAR summary =
SUMMARIZE (
ALLSELECTED ( Table ),
[Category],
"AVG",
CALCULATE (
AVERAGE ( 'Table'[Number] ),
'Date'[DayOfWeekNumber] = 7,
'Table'[Hour] >= 4,
'Table'[Hour] <= 6
)
)
RETURN
AVERAGEX ( summary, [AVG] )

Total_maximum=
VAR summary =
SUMMARIZE ( ALLSELECTED ( table ), [Category], "MAX", MAX ( 'table'[number] ) )
RETURN
SUMX ( summary, [MAX] )
``````

Notice: replace above 'category' part with your visual axis and legend fields.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper IV

looking at the bar chart again, I cannot figure out why it is not charting the data correctly.  I think this is the main problem.  The above formulas would help with this report that I am working on, but if I can get the Bar Chart to show the data correctly would be great. for now.

Community Champion

Hi @moltra

it's not very clear what you want to achieve. Take a look at the following link:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors