topic Re: SUM Only Specified Values For All Higher Matrix Levels in DAX Commands and Tips

SUM Only Specified Values For All Higher Matrix Levels

Take2 — Fri, 21 Jul 2023 17:40:34 GMT

Hello -

Here is the link to the PBIX:

https://drive.google.com/file/d/1vdZmj0n0-JpCXnQ6kqTgxxjGW_8x-rx8/view?usp=drive_link

The following Rows exist in the below Matrix:

- SalesRep Name

- Customer Name

- Product Name

ISSUE: The values highlighted in yellow are not correct. Instead, they should be the SUM of Isolated Type 2 Desired Values ([z1 Isolate T2 Desired Values]).

DESIRED RESULT:

Measures are as follows:

So the last measure [z1 Isolate T2 Desired Values] is correctly identifying the rows that should be SUMMED.

However, I am not able to figure out how to SUM these values for all higher levels in the Matrix.

Thanks for the help

Re: SUM Only Specified Values For All Higher Matrix Levels

Take2 — Fri, 21 Jul 2023 20:37:32 GMT

Well, I finally figured it out. Only took me most the day of trial & error. Oh DAX.

Thanks to anyone who was trying to help me.

Re: SUM Only Specified Values For All Higher Matrix Levels

Take2 — Fri, 21 Jul 2023 22:06:31 GMT

OK, so it looks like I still need help. My solution in the previous post only works on the dummy data set I created for sharing. However, when I try to add the same simple measure to the matrix in the REAL report, the Matrix just spins endlessly (more than 10 minutes), and Performance Analyzer just spins as well.

The spinning starts the instant I attempt to add the measure [z2 Totals] to the Matrix.

After spinning for 15 minutes, the matrix eventually fails:

"Cannot display the visual. See Details."

I am confused how this can be running out of memory.

In the current FC (Filter Context), there are only 3 values for 'DimProduct STAGE'[PRODUCT_CODE] over which I want to be iterating. So how am I running out of memory?

1) $18.89

2) $4.58

3) $6.87

If anyone can help me fix the measure so that it only handles the PRODUCT_CODE values displayed, I would greatly appreciate it. This measure should take less than a second to process the resul. So clearly my measure is very wrong....at least when performed against the real data set.

Thank you

Re: SUM Only Specified Values For All Higher Matrix Levels

tamerj1 — Thu, 27 Jul 2023 16:24:06 GMT

Take2
Hi Nathan. Apologies for the late response. I really didn't have the time to look into it. Please look at the proposed solution below

Type 2 V2 New = VAR Type1Exists = NOT ISBLANK ( [Type 1] ) VAR Type2AllQuarters = SUMX ( SUMMARIZE ( Data, SalesRep[SalesRep Name], Customer[Customer Name], 'Product'[Product Name] ), CALCULATE ( [Type 2], REMOVEFILTERS ( 'Calendar'[Year Quarter Number] ) ) ) VAR Result = IF ( Type1Exists, Type2AllQuarters ) RETURN Result

Re: SUM Only Specified Values For All Higher Matrix Levels

Take2 — Fri, 28 Jul 2023 14:48:46 GMT

tamerj1

1) Please do not apologize for any delay. I genuinely appreciate you getting back to me.

2) This is great! Thank you very much for the help!

3) Trying to understand a bit more about your code . From what I can see, the only difference in your code is that it adds SUMX & SUMMARIZE.

NOTE: Below is the real code modified according to your instructions, which is why the var & column names are changed. It's working beautifully, thanks to you.

02 Installed Tamerj1 =

VAR WrittenExists = [Written Zeros STAGE] > 0

VAR InstalledAllQuarters =

SUMX(

SUMMARIZE(

'FactWrittenSales STAGE',

'DimSalesRep STAGE'[SalesPersonName],

'DimCustomer STAGE'[CustomerName],

'DimProduct STAGE'[PRODUCT_CODE]

CALCULATE(

[Installed Zeros STAGE],

REMOVEFILTERS('DimCalendar STAGE'[FiscalQTRYearName])

)

VAR Result =

IF(WrittenExists, InstalledAllQuarters)

RETURN

Result

If I perform only the SUMMARIZE by itself in Dax Studio, it returns 39,705 rows for the 3 summarized columns (SalesPersonName, CustomerName, PRODUCT_CODE).

Does this mean that the SUMX iterates over all 39,705 rows, performing my CALCULATE statement for each row & then summing the total?

Or does it mean that the SUMX iterates only over the rows in the current Filter Context of the matrix (a tiny subset of the 39,705 which only contains 3 PRODUCT_CODE values in this example)?

Regards,

Nathan