Forum Discussion
Table visual totals incorrect
- 2 years ago
Anonymous When you SUMMARIZE, you need to have the summarization group by the same columns you use in your table visual such as As of Date, Portfolio, etc. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
EDIT: Please disregard - I did some more digging and realized that I was miscalculating what the results should be and the solution you provided is giving the correct result! Thanks very much for the help.
Hi Greg_Deckler thanks for your reply. This got me 90% of the way there and I'm fully on board the measures totals are broken train. I'm still missing something in my scenario though if you can help out.
I implemented the SUMMARIZE with HASONEVALUE solution in the measure # YTW Test. I ended up with the same total value I was getting from the second measure I provided in my original post, although this one I'm able to add to my visual and get the correct outputs on a row level. The correct sum is 2.35 though and not 5.67 so I'm still missing something.
I included in the summarize the fields in my table which are setting the granularity - Date, Portfolio, and Security ID and it didn't change the total output. I believe that because %Assets and YTW reside in fact tables and are linked via the Security dimension, I need to summarize on the dimension table, but by doing so I'm not able to include groupings on other dimensions such as calendar and portfolio so I'm not sure how to approach that.
# YTW Test =
VAR __table =
SUMMARIZE(
factPortfolioHoldings,
dimSecurity[SentryId],
dimCalendar[Date],
dimPortfolio[Id],
"__value",[# %Assets * YTW]
)
RETURN
IF(HASONEVALUE(dimSecurity[SentryId]),[# %Assets * YTW],SUMX(__table,[__value]))
I linked a sample file too in case you want to take a look. In anonymizing the data some of it was removed so the outputs are slightly different but the model should be functionally identical.
https://drive.google.com/file/d/1FIqBBBlLHQyYdDIjlzCjXms5mTK9ImWi/view?usp=sharing
Thanks again.
Edit:
It might help to note that the column YTW in the visual is not a measure, just a straight column from factSecurityDetails, and it is also getting a really wildly innacurate total.
Anonymous Two things. One, I don't see Name in your SUMMARIZE but it is in your table. Not sure if that would make a difference. The other thing I would try is to use the related columns from your fact table in your SUMMARIZE. The ones that correspond with the dimension columns you are using.
- Anonymous2 years agoNot applicable
Hi Greg_Deckler, I was doing some more digging and realized that I was miscalculating what my expected results were - it was a simple issue with datatyping when I exported the table values to excel which I overlooked. Your solution is giving the correct result.
Thanks very much!!