Solved: How to bar chart data based on filtered data

moltra · ‎09-02-2020

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 · ‎09-06-2020

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.

View solution in original post

v-shex-msft · ‎09-06-2020