Reply
Member
Posts: 95
Registered: ‎05-03-2018
Accepted Solution

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.

 

overview charts.jpg


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

Re: Do a calculate(sum) over measure

[ Edited ]

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:

1.PNG

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:

1.PNG

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

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.

View solution in original post


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

Re: Do a calculate(sum) over measure

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 

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 ]

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:

1.PNG

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:

1.PNG

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

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 !