Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Wrong total column

I have the following visual:

 

 These are the values of MEDIA_PRAZO_MEDIO column:

1.581775
6.259016
2.271065
3.943498
1.024467
1.413401
0.437936
1.559115
0.03187
1.475571
0.483862
0.532457
0.166125
0.260911
0
0.052677
0.076951

 

 

 

On my visual you can notice that the grand total of this column is 20.736, but if you sum the columns values by hand, you can realize that it's wrong, the real grand total value is 21.57. This is my measure:

 

MEDIA_PRAZO_MEDIO = 
VAR
	grand_total = CALCULATE(SUM(DADOS[PRECOTOTAL]), ALL(DADOS[PRECOTOTAL]), ALL(DADOS[PRAZO_MED]))
VAR
	_sum = SUM(DADOS[PRECOTOTAL])
VAR
	percentage = _sum / grand_total
RETURN
	AVERAGE(DADOS[PRAZO_MED]) * percentage

What can I do to fix it?

Need Help
Show and Tell

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi Anonymous,

     

    Just simulated your calculation in PBI using you data and your measure and my final resulta was 27.824 (image below) breaking down your formula when you are doing the AVERAGE(DADOS[PRAZO_MED]) you make the average of the value multiplied by the %GT so for row 1 you have = 7 * 22,60% = 1,582 but for the total column you have 27.82 (average of the PRAZO_MED) * 100% = 27.824.

     

     

    Regards,

    MFelix

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      MFelix, neither 20.73 or  27.82 are corrects, calculate that sum on excel, you will see that the right sum is 21.57. So, what am I doing wrong to calculate my weighted averege?

      • MFelix's avatar
        MFelix
        Super User
        9 years ago
        Hi @caarlos,

        You are refering to different results when you use a measure the calculations is.made based on the context of the visual so if you calculate an average value on a row level the total in the end will also be an average and not a sum.of the previous values, that's why in my print I added the Average calculated on the table and not as a measure. If you want to have the average calculated on the lines but on the tital the sum you need to do it in a different way.

        Is the data you show all the lines in your table or are this aggregationa on a larger detail?

        If it's your table values you can add the average as a column not a measure and then when you add it to your table it will sum the correct value.

        Regards,
        MFelix