Forum Discussion

moltra's avatar
moltra
Icon for Helper IV rankHelper IV
6 years ago
Solved

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.

 

 

DateHourNumberObject
8/27/2020 1:0066Object1
8/27/2020 1:0012Object2
8/27/2020 3:0067Object1
8/27/2020 3:006.01Object2
8/27/2020 5:00101Object1
8/27/2020 5:008Object2
8/27/2020 7:0061Object1
8/27/2020 7:0010.01Object2
8/27/2020 9:0060Object1
8/27/2020 9:0043Object2
8/27/2020 11:00111Object1
8/27/2020 11:0010Object2
8/27/2020 13:0078Object1
8/27/2020 13:0013Object2
8/27/2020 15:0080Object1
8/27/2020 15:007.01Object2
8/27/2020 17:0028Object1
8/27/2020 17:007.01Object2
8/27/2020 19:0060Object1
8/27/2020 19:009Object2
8/27/2020 21:0058Object1
8/27/2020 21:0027Object2
8/27/2020 23:0058Object1
8/27/2020 23:0034Object2
8/28/2020 1:0063Object1
8/28/2020 1:0013Object2
8/28/2020 3:0061Object1
8/28/2020 3:008Object2
8/28/2020 5:00115Object1
8/28/2020 5:006.01Object2
8/28/2020 7:0060Object1
8/28/2020 7:009.01Object2
8/28/2020 9:0062Object1
8/28/2020 9:0046Object2
8/28/2020 11:0099Object1
8/28/2020 11:0027Object2
8/28/2020 13:0068Object1
8/28/2020 13:0017Object2
8/28/2020 15:0077Object1
8/28/2020 15:008Object2
8/28/2020 17:0032Object1
8/28/2020 17:009Object2
8/28/2020 19:0030.01Object1
8/28/2020 19:008Object2
8/28/2020 21:000.04Object1
8/28/2020 21:006.01Object2
8/28/2020 23:0029Object1
8/28/2020 23:007.01Object2
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • moltra's avatar
    moltra
    Icon for Helper IV rankHelper IV
    6 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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