Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nano
Frequent Visitor

Filter part of a chart

Hi all,

I hope someone can help me with this problem

I have the monthly data of the water consumption of different towns. I need to make a bar graph for each town representing the year consumption for 2016, 2017 and 2018 of the town, comparing it with the average consumption of all the towns. I thought it would be easy but I am having problems. 

 

The first part is easy:

Axis: [year]

Value: [monthly water consumption] (I choose sum)

And I use a filter to select the town that I want to represent:

For "town 1" I get this:

borrarr.png

But for the second part, I have problems trying to make the new 3 bars representing the year average consumption while not being affected by the town filter, and being separated for every year. I tried to do a “quick measure”:

Monthly consumption average per town =

AVERAGEX(

    KEEPFILTERS(VALUES('table'[town]));

    CALCULATE(SUM('table'[monthly conpsumtion)]))

)

But when I insert this quick measure in the graph, it gets filtered by the town (since the graph is filtered).

 

I guess I need to use the “all function” but It is not working, I don´t know how to use it for this specific problem.

 

Thank you

 

 

1 ACCEPTED SOLUTION

Hi @Nano ,

By my tests with your data sample you could create the measure below to get your output below.

average =
VAR a =
    CALCULATE (
        SUM ( 'table'[Monthly Water Consumption] ),
        ALLEXCEPT ( 'table', 'table'[Year] )
    )
VAR b =
    CALCULATE ( DISTINCTCOUNT ( 'table'[Town] ), ALL ( 'table' ) )
RETURN
    DIVIDE ( a, b )

Here is the output.

Untitled.png

Hope this can help you.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Nano ,

It seems that you want to calculate the average consumption of all the towns which will not be affacted by slicer.

Your guess is right, we need to use ALL function in the measure.

What about the measure below?

Monthly consumption average per town =
AVERAGEX (
    ALL ( 'table' ),
    CALCULATE ( SUM ( 'table'[monthly conpsumtion)] ) )
)

If you still need help, please share the data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much for your help. However, by using this measure I am getting this graph which is not what I need:

borrar.png

I will try to use an example from my data:

The data I have is:

Town

Year

Month

Monthly Water Consumption

Town1

2016

jan

1

Town1

2016

feb

1

Town1

2016

mar

1

Town1

2016

apr

1

Town1

2016

may

1

Town1

2016

jun

1

Town1

2016

jul

1

Town1

2016

ago

1

Town1

2016

sep

1

Town1

2016

oct

1

Town1

2016

nov

1

Town1

2016

dec

1

Town1

2017

jan

2

Town1

2017

feb

2

Town1

2017

mar

2

Town1

2017

apr

2

Town1

2017

may

2

Town1

2017

jun

2

Town1

2017

jul

2

Town1

2017

ago

2

Town1

2017

sep

2

Town1

2017

oct

2

Town1

2017

nov

2

Town1

2017

dec

2

Town2

2016

jan

10

Town2

2016

feb

10

Town2

2016

mar

10

Town2

2016

apr

10

Town2

2016

may

10

Town2

2016

jun

10

Town2

2016

jul

10

Town2

2016

ago

10

Town2

2016

sep

10

Town2

2016

oct

10

Town2

2016

nov

10

Town2

2016

dec

10

Town2

2017

jan

11

Town2

2017

feb

11

Town2

2017

mar

11

Town2

2017

apr

11

Town2

2017

may

11

Town2

2017

jun

11

Town2

2017

jul

11

Town2

2017

ago

11

Town2

2017

sep

11

Town2

2017

oct

11

Town2

2017

nov

11

Town2

2017

dec

11

 

And what I need to get in a chart (once I choose the town in the slicer) is:

Year

Consumption (of the choosen town)

Average consumption  (of all the towns)

2016

12

66

2017

24

78

 

The first column data is easy to represent in a bar chart, but the second column data is the problem. I don’t know what kind of measure I have to create.

 

Thank you again and best regards.

Hi @Nano ,

By my tests with your data sample you could create the measure below to get your output below.

average =
VAR a =
    CALCULATE (
        SUM ( 'table'[Monthly Water Consumption] ),
        ALLEXCEPT ( 'table', 'table'[Year] )
    )
VAR b =
    CALCULATE ( DISTINCTCOUNT ( 'table'[Town] ), ALL ( 'table' ) )
RETURN
    DIVIDE ( a, b )

Here is the output.

Untitled.png

Hope this can help you.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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