- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Regards
Miguel Félix
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Regards
Miguel Félix
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You need to use a SUMX calculation in order to get to the result you are looking for.
I have put a workbook togather with all the calculations you need: https://1drv.ms/u/s!AnKdZAdhTr78dKvMfL9WlrvogE8
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@OpenDataLab, thanks it worked. But, I though it would work for my real table. I purposely hid two others columns =(
Look, at my real data here.
I have two more columns, one named NATUREZA and other named EMISSAO. The EMISSAO column is a date and the NATUREZA column containg some text values.
On the example that gives 21.57 as a grand total, I have the following filters:
One page filter with NATUREZA ( look at the NATUREZA's values, I've edited it to be easy), just check the string values that contains USE_1, USE_2, USE_3 and USE_4 on the page filter.
And finally I created a slicer with the values of EMISSAO, the values are: Year = 2017, Month = 6 and Days = 1 to 19.
Can you help me to create the right measuere? I've already tried to use ALLEXCEPT, but it did not works....
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous,
So what columns do you want to show with the measures on the Table visual on the report, with NATUREZA and EMISSAO column or not? Could you post the expected result against your shared sample data?
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@v-ljerr-msft, currently I have this visual:
On the left side there is a EMISSAO slicer, the values should be between 01/06/2017 and 19/06/2017. And there is page filter using the NATUREZA column, its values that must be checked are USE_1, USE_2, USE_3, USE_4. The only thing that is different of what I want is the sum of MEDIA_PRAZO_MEDIO. The value that I want is 21.56, not 20.74.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
07-21-2018 03:49 AM | |||
01-23-2024 10:40 AM | |||
01-18-2024 05:44 AM | |||
06-02-2024 07:13 PM | |||
12-20-2022 10:30 AM |
User | Count |
---|---|
140 | |
110 | |
81 | |
61 | |
46 |