topic Problem with SUMX in DAX Commands and Tips

Problem with SUMX

pcavacas — Mon, 08 Jun 2020 20:27:02 GMT

I have a couple a problem with a SUMX command that I cannot figure out.

First thing I have is another DAX Measure called Latest Estimate Revenue. The details of this command are not overly important, it performs some calculations on different tables, the key point to this is that it relates to a Product table and to a Sold To and a Calendar table all of which are working fine. I can put it in a Matrix visual and drill around these 2 tables and it is working fine.

Second I have a table called APF. This table has a few fields AOR2ID, PEID, ProductCategoryID, Date and a Rate.

What I'm trying to is for each rate in the APF table I want to multiple the Rate * the corresponding Latest Estimate Revenue. The fields AOR2ID and PEID point to a set of Sold Tos and the ProductCategoryID points to a set of products, so what I want to do is get the Latest Estimate Revenue filtered to just this AOR2/PEs and ProductCategories and multiple it times the rate.

Here is the DAX that I have right now.

APF = SumX('VF APF Rates',
'VF APF Rates'[Rate]
*
CALCULATE([Planned Cases],
Filter('Sold To',
And('Sold To'[Aor2ID] = 'VF APF Rates'[Aor2ID],
'Sold To'[PEID] = 'VF APF Rates'[PEID]
)
),
Filter('Product', 'Product'[ProductCategoryID] = 'VF APF Rates'[ProductCategoryID])
)
)

This returns nothing.

If I remove the Calculate part and put in a hard coded 1 then it returns a value as expected.

If I change it to something like what is below where I'm just calculating the Latest Estimate Revenue hard coded to a specific set of values, this returns the value that I would expect as well.

APF =

// SumX('VF APF Rates',

// 'VF APF Rates'[Rate]

// *

CALCULATE([Latest Estimate Revenue],

Filter('Sold To',

And('Sold To'[Aor2ID] = 257,

'Sold To'[PEID] = 1

)

Filter('Product', 'Product'[ProductCategoryID] = 5)

)

// )

My understanding of the SumX method is that it would loop over the rows in the VF APF Rate table and perform the calculation for each row. So my second DAX above is half the calculation for 1 specific row and that returns correctly and I know from my other test that the first part of the calculation works fine.

What am I missing and how can I get this to work?

Re: Problem with SUMX

stevedep — Mon, 08 Jun 2020 20:34:09 GMT

It depends on the relationship between the tables and the definition of measures. With each iteration it will use the relationship to perform the measure calculation you defined.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Problem with SUMX

pcavacas — Mon, 08 Jun 2020 20:43:57 GMT

What I want is for it to not take any other filters based on relationships that may exist, instead I want it to calculate the Latest Estimate Revenue based solely on values from the APF Rates table.

Re: Problem with SUMX

stevedep — Mon, 08 Jun 2020 20:45:54 GMT

Probably requires some explicit summary table to ensure calculatios are done on the right level.

Re: Problem with SUMX

pcavacas — Mon, 08 Jun 2020 21:07:00 GMT

I got most of it working thanks to the help that you have provided so far, just need help with one final part. What i have now is

APF =

SumX('VF APF Rates',

'VF APF Rates'[Rate]

// 1

CALCULATE([Latest Estimate Revenue],

All(),

Filter('Sold To',

And('Sold To'[Aor2ID] = 'VF APF Rates'[Aor2ID],

'Sold To'[PEID] = 'VF APF Rates'[PEID]

)

Filter('Product', 'Product'[ProductCategoryID] = 'VF APF Rates'[ProductCategoryID]),

Filter('Calendar', 'Calendar'[MonthSeqId] = 275)

)

The problem is with the hard coded MonthSeqId at the end. The problem here is that in the APF Rates table there is only a single date for the month. What need to do is take that 1 day from the APF Rate table and go to the calendar and get the MonthSeqId (which is a field on the Calendar table to denote the fiscal months) and then put that value into the filter, but am having problems writing that.

Re: Problem with SUMX

stevedep — Tue, 09 Jun 2020 05:45:03 GMT

You can try to work with variables and selected value etc.

Re: Problem with SUMX

stevedep — Fri, 12 Jun 2020 07:23:59 GMT

Did you manage to get it working? If not, can you share your data model?