topic Re: My Matrix Row Values are Incorrect in DAX Commands and Tips

My Matrix Row Values are Incorrect

Anonymous — Fri, 18 Feb 2022 16:13:31 GMT

So it seems this topic has been addressed on several occasions, but I will admit that some of the solutions are hard to follow. I created this matrix table, and while the bottom total is correct, the row columns are not.

I know it has to do with the context of the data, but can someone advise me on how to fix this issue?

Re: My Matrix Row Values are Incorrect

Russell-PBI — Fri, 18 Feb 2022 17:25:07 GMT

Hi Anonymous, it's a bit difficult to discern with just the table given. Can you provide a model diagram and sample of the data?

In a lot of cases where the total sums up differently to the sum of the individual lines, it is because of the relationship between tables. For example, one record in your measure table may be related to more than one record in your attribute table and those records have different 'Obligated value' values (e.g., $0-$25K and $25K-$250K). The total is unaffected by the attribute, so just counts the number of records in the actual table, whereas the rows are affected by the attribute and will be counted against each attribute value where there is a relationship.

Re: My Matrix Row Values are Incorrect

tamerj1 — Fri, 18 Feb 2022 18:23:16 GMT

Hi Anonymous

What is the measure?

Re: My Matrix Row Values are Incorrect

Anonymous — Fri, 18 Feb 2022 18:26:59 GMT

The measure is the distinct count of transactions that are new/recompetes

Re: My Matrix Row Values are Incorrect

Anonymous — Fri, 18 Feb 2022 21:23:13 GMT

Hi Russell-PBI, please see the attached. I had to clean up my client's data to a dummy version, but these are the same filters that I applied. The total value is fine, but as you can see the filters aren't being applied at the row level.

Re: My Matrix Row Values are Incorrect

Russell-PBI — Fri, 18 Feb 2022 23:16:44 GMT

That's a strange one. From the screenshot alone, I don't see any reason why the rows wouldn't sum up to the total. If it's now dummy data, would you like to share your PBI file and data, and I'll see if I get the same result? You can send me a DM if you'd prefer.

Re: My Matrix Row Values are Incorrect

tamerj1 — Sun, 20 Feb 2022 15:01:03 GMT

Anonymous

This is why they're not the same. You are expected to have more in the raws than in the grand total. You have values repeated in different rows because DISTINCT will be applied on each cell seperately but this does not happen at the gand total where the distinct count will be curried over all values which is similar to taking the distinct count of all other cells therefore you will allways have less in the grand total

Re: My Matrix Row Values are Incorrect

v-chenwuz-msft — Wed, 23 Feb 2022 08:12:45 GMT

Hi Anonymous ,

Is that you want to the total should be all of the table unless the part of which type is new/recompete. But the new/recompete column values is right.

Maybe you can try to use HASONEVALUE to control the total column. Some measure like the following and replace the values of the martix table:

Measured = IF( HASONEVALUE( 'table'[Type of Mod] ), DISTINCTCOUNT( 'table'[Record Number] ), CALCULATE( DISTINCTCOUNT( 'table'[Record Number] ), ALLEXCEPT( 'table', 'table'[Obilgated Value] ) ) )

If you need more help, please share sample date and expect result.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: My Matrix Row Values are Incorrect

Russell-PBI — Wed, 23 Feb 2022 09:15:23 GMT

Here is the solution I came up with:

Using as a filter value:

Max Obligated Flag = VAR MaxObligatedValue = CALCULATE(MAX(Table1[Obligated]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod])) RETURN IF(Table1[Obligated] = MaxObligatedValue, TRUE, FALSE)

Or, using as a grouping attribute:

Max Value by Record Number = VAR MaxObligated = CALCULATE(MAX(Table1[Obligated]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod])) RETURN CALCULATE(MAX(Table1[Value]), ALLEXCEPT(Table1, Table1[Record Number], Table1[Type of Mod]), Table1[Obligated] = MaxObligated)

Re: My Matrix Row Values are Incorrect

Anonymous — Thu, 24 Feb 2022 02:57:27 GMT

You're awesome! This was very helpful!