Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
moltra
Helper IV
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.

 

 

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
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
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.
moltra
Helper IV
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.

 

FrankAT
Community Champion
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)

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors