Forum Discussion

moltra's avatar
moltra
Helper IV
5 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....
Need Help
  • Anonymous's avatar
    Anonymous
    5 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

Anonymous's avatar
Anonymous
Not applicable
5 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