Do a calculate(sum) over measure

[RemiAnthonise]

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.

[v-danhe-msft]

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 

[RemiAnthonise]

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

[RemiAnthonise]

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.

[RemiAnthonise]

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

[v-danhe-msft · Accepted solution]

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

[RemiAnthonise]

Thanks a lot Daniel @v-danhe-msft !