Desktop

Member
Posts: 95
Registered: ‎05-03-2018

Do a calculate(sum) over measure

Hi guys,

I'm stuck and I hope you can help me.

I have a facttable with declarations and I have a facttable with client id's.

With this measure I calculate the average costs per client (the big bars in my image below)

measure 1
Gemiddelde kosten per cliënt =
Average costs per client =
DIVIDE(
CALCULATE(SUM(FactDeclarationLine[Amount]));
CALCULATE(
DISTINCTCOUNT(FactClient[ClientId])))

I display these in a chart with cities in my legend. In my report filter I have selected 7 cities.

I show these in my second chart with the following measure:

measure 2

Gemeentes = CALCULATE
(
DISTINCTCOUNT(DimCity[Name]);
FILTER(DimCity;DimCity[Name] <> BLANK()))

So far, so good. Now the problems pops up. I want to present a linevalue with the average of these 7 cities.

Divide measure 1 by measure 2

Test gemiddelde in Zeeland =
DIVIDE(

[Gemiddelde kosten per cliënt]
;
[Gemeentes])

You can see the same values in chart 1 and chart 2, I gave them the same colour.

The problem is: with my 3rd measure, it gives me the average and it divides these by 7. I want to do an calculate(sum) in the 3rd measure so I have al the corresponding values.

Divide measure 1 by measure 2

Test gemiddelde in Zeeland =
DIVIDE(
calculate(sum(

[Gemiddelde kosten per cliënt]))
;
[Gemeentes])

My expected result would be about 750 / 800 in 2016 and about 4000 in 2017. 7 times higher than it's now (7 times because of the 7 cities it calculates with).

I hope this is clear to you guys. I can't give you an copy of my data because it's sensitive. Maybe I'll create an sample file if this isn't clear to you.

Thanks, cheers.

Accepted Solutions
Community Support Team
Posts: 2,061
Registered: ‎06-24-2018

Re: Do a calculate(sum) over measure

[ Edited ]

I have tested your pbix file and I could not understand why your expected result would be about 750 / 800 in 2016 and about 4000 in 2017.

Based on my test, with your Measure:

```Average in state =
DIVIDE(
CALCULATE
(
[Average costs per client]
),
CALCULATE
(
DISTINCTCOUNT(DimCity[Name]),
FILTER(DimCity,DimCity[Name] <> BLANK())),0)```

The highlight part in your situation seemed right:

If you want to get the correct result, I think the denominator is correct and you could modify your measure as below in numerator :

```Average in state =
DIVIDE(
SUMX(ALL('DimCity'),[Average costs per client]),
CALCULATE
(
DISTINCTCOUNT(DimCity[Name]),
FILTER(DimCity,DimCity[Name] <> BLANK())),0)```

Result:

https://www.dropbox.com/s/9f7w0hc6sfcwsps/Average%20sample.pbix?dl=0

Regards,

Daniel He

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

All Replies
Community Support Team
Posts: 2,061
Registered: ‎06-24-2018

Re: Do a calculate(sum) over measure

From your description, it is too hard to reproduce your problem, could you please share your sample pbix file to have a test if possible?

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 95
Registered: ‎05-03-2018

Re: Do a calculate(sum) over measure

Hi Daniel @v-danhe-msft,

I will do my best the upcoming days to reproduce the problem. Thanks so far, I'll let you know.

Cheers,

Remi

Member
Posts: 95
Registered: ‎05-03-2018

Re: Do a calculate(sum) over measure

[ Edited ]

Hi Daniel @v-danhe-msft,

Here is my sample. Important to know: I have 7 cities in my table.

It has 2 pages in my report. I've solved page 1, so you can focus on page 2 :-)

Page 2 shows a chart with 7 bars, 1 bar for each city. Each city has it's own value. I expect the line-value to show the average of these 7: it must do a sum of these 7 charts and divide them by 7. Currently, it only divides by 7.

Highlighted
Member
Posts: 95
Registered: ‎05-03-2018

Re: Do a calculate(sum) over measure

Hi Daniel @v-danhe-msft ,

I don't want to be impatient but if you find some time to help that would be really nice. I need to finish this report within one week.

Cheers,

Remi

Community Support Team
Posts: 2,061
Registered: ‎06-24-2018

Re: Do a calculate(sum) over measure

[ Edited ]

I have tested your pbix file and I could not understand why your expected result would be about 750 / 800 in 2016 and about 4000 in 2017.

Based on my test, with your Measure:

```Average in state =
DIVIDE(
CALCULATE
(
[Average costs per client]
),
CALCULATE
(
DISTINCTCOUNT(DimCity[Name]),
FILTER(DimCity,DimCity[Name] <> BLANK())),0)```

The highlight part in your situation seemed right:

If you want to get the correct result, I think the denominator is correct and you could modify your measure as below in numerator :

```Average in state =
DIVIDE(
SUMX(ALL('DimCity'),[Average costs per client]),
CALCULATE
(
DISTINCTCOUNT(DimCity[Name]),
FILTER(DimCity,DimCity[Name] <> BLANK())),0)```

Result:

https://www.dropbox.com/s/9f7w0hc6sfcwsps/Average%20sample.pbix?dl=0

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 95
Registered: ‎05-03-2018

Re: Do a calculate(sum) over measure

Thanks a lot Daniel @v-danhe-msft !