Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Table visual totals incorrect

I've read a lot of posts from people with similar issues but I'm still struggling.

 

I have this visual which is not producing the correct totals. The column I'm focusing on for the purpose of this questions is #Assets * YTW, although others are also impacted. YTW for example is just a column, there's no calculation being done to and it is still producing the wrong total.

 

That column is a measure which multiplies # % Assets and YTW.  The values produced in each row of the visual are correct. But the total value is wildly wrong. It should be 2.35 but is showing 22,941.15.

 

 

 

 

#%Assets * YTW = 
SUMX(
    SUMMARIZE(dimSecurity, "Arg1", [# % Assets], "Arg2", [YTW])
    , [Arg1] * [Arg2]
)

 

 

 

 

 

Based on what I've read, the problem is probably an iteration issue, but I'm struggling to figure out how to apply an iterator when the measure is already being iterated.

 

Here is an alternative measure I tried, which produced a more reasonable total, though still wrong when applied to a card visual. When I applied it to the table however it wouldn't populate any data. I think maybe if I was able to iterate on the combination of VALUES from dimSecurity and dimCalendar, as the AsOfDate is also a filter criteria for this visual, but I'm not sure how to accomplish that.

 

 

 

 

# %Assets*YTW SUMX Test = 
SUMX(
    VALUES(dimSecurity[SentryId]),
    CALCULATE(
        SUMX(
            SUMMARIZE(dimSecurity, "Arg1", [# % Assets], "Arg2", [YTW])
            , [Arg1] * [Arg2]
        )
    )
)

 

 

 

 

To get an idea of the model: 

  • [% Assets] comes from factPortfolioHoldings
  • [YTW] comes from factSecurityDetails
  • these two measures are related through dimSecurity on the SecurityId (SentryId)
  • dimPortfolio provides the Portfolio field in the visual
  • dimCalendar provides the date field in the visual

 

Any help is much appreciated, thank you in advance!

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        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.