cancel
Showing results for
Did you mean:
Highlighted Helper V

## 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. 1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Microsoft

## Re: Do a calculate(sum) over measure

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.
6 REPLIES 6
Highlighted Microsoft

## 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.
Highlighted Helper V

## 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

Highlighted Helper V

## Re: Do a calculate(sum) over measure

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 Helper V

## 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

Highlighted Microsoft

## Re: Do a calculate(sum) over measure

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.
Highlighted Helper V

## Re: Do a calculate(sum) over measure

Thanks a lot Daniel @v-danhe-msft !

Announcements #### Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers! #### June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (1,034)