- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Do a calculate(sum) over measure

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2019 06:45 AM

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.

Solved! Go to Solution.

Accepted Solutions

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-20-2019 08:24 PM - edited 01-20-2019 09:19 PM

Hi @RemiAnthonise,

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:

You could also download the pbix file to have a view.

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

Regards,

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-06-2019 10:16 PM

Hi @RemiAnthonise,

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-09-2019 08:04 AM

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

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-14-2019 02:59 AM - edited 01-14-2019 03:02 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-18-2019 04:41 AM

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

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-20-2019 08:24 PM - edited 01-20-2019 09:19 PM

Hi @RemiAnthonise,

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:

You could also download the pbix file to have a view.

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

Regards,

Daniel He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-21-2019 04:04 AM

Thanks a lot Daniel @v-danhe-msft !