How to bar chart data based on filtered data

[moltra]

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

[v-shex-msft · Accepted solution]

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

[moltra]

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.

[FrankAT]

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)