Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RemiAnthonise
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

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
v-danhe-msft
Employee
Employee

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.

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

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.

 

 

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

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.

Thanks a lot Daniel @v-danhe-msft !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.