cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
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.

 

overview charts.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Do a calculate(sum) over measure

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

6 REPLIES 6
Highlighted
Microsoft
Microsoft

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

Re: Do a calculate(sum) over measure

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

Highlighted
Helper V
Helper V

Re: Do a calculate(sum) over measure

Thanks a lot Daniel @v-danhe-msft !

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

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

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

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors