Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
Solved! Go to 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.
Hope this can help you.
Best Regards,
Cherry
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
Thank you very much for your help. However, by using this measure I am getting this graph which is not what I need:
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.
Hope this can help you.
Best Regards,
Cherry
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |