• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMX on a measure calculated from other measures

E

ELanc

New Member
Hi. I'm kind of new to Power Pivot/DAX and I'm having some trouble with a SUMX to get the correct total for my measure. My measure is made up of two other measures all from the same table. Following is detail of the situation with some of the names simplified since I can't paste from my employer. The base of the measure is fairly basic =ROUND([Amount]*[Percent],2). My entire measure is =IF(HASONEFILTER(Table[Account Num]),ROUND([Amount]*[Percent],2),SUMX(Table,ROUND([Amount]*[Percent],2))). The account level values are correct but the total amount from the SUMX is not.... but I can't figure out why. I stripped out all other filters etc. to make sure it wasn't a pivot context issue, still no luck. To test further, I copied the pivot data (Account Num, Amount & Percent) and pasted values into a separate file, loaded it into a data model and tried same measure =IF(HASONEFILTER(.... ) . In this case with the calculation being column based from the data model, the total from SUMX was correct. Is there some sort of "trick" to using SUMX with calculation that is based solely on other measures (rather than data model table columns)? Please help.
 
Try using table variable in Dax.

Using summarize etc to create context for SUMX to iterate over.

It would help, if you can upload sample data, along with your desired results.
 
I’ll give summarize a try.
Otherwise, I can’t send files externally from work and I can’t even get to all of Chandoo - forums are blocked. I’m doing this correspondence from home pc. I’ll see if I can fabricate a sample tomorrow that behaves the same mannner.
 
You must log in or register to reply here.
Back
Top