topic Re: Sum visible rows in matrix visual in DAX Commands and Tips

Sum visible rows in matrix visual

Maieev — Wed, 15 Feb 2023 11:36:12 GMT

Hi,

I have a matrix table, with multiple rows and values. I wanted to count number of products but the measure I wrote is not summing in the table nor in card visual. Rows are coming from 6 different tables.

Count products = COUNTX(Metrics,[Request Quantity])

Some of the products repeat within the country and customer, but I want to count visible number of products.

Also I tried to calculate the average of the Days past request date but it gives me very high number, so I suppose it takes data from the whole table and I wanted to have it only for the data from the table.

Days past request date = DATEDIFF('Time - Requested By'[Requested By Date],TODAY(),DAY)

Avg = AVERAGEA('Time - Requested By'[Days past request date])

I would appreciate any help.

Re: Sum visible rows in matrix visual

tamerj1 — Wed, 15 Feb 2023 14:19:31 GMT

Hi Maieev
Would you please indicate from which table each column in the matrix rows is coming from? Please present the relationshipd of these tables.

Re: Sum visible rows in matrix visual

Maieev — Wed, 15 Feb 2023 14:43:49 GMT

Sure,

Rows:

1. 'Sales Structure'[Country]

2. 'Customer Sold To'[Customer Sold To]

3. 'Sales Order Details'[Order Number]

4. 'Time - Order Entry'[Order Entry Date]

5. 'Time - Requested By'[Requested By Date]

6. 'Time -Requested By'[Days past request date] it's calculated column = DATEDIFF('Time - Requested By'[Requested By Date],TODAY(),DAY)

7. 'Product'[Product ID Description]

Values:

From 'Metrics' table, two measures:

- Request Quantity

- Request Value

and

- Count products = COUNTAX(Metrics,[Request Quantity])

It's a very big relationship model, this is a part of it, all of those are linked to Sales Order Details.

Re: Sum visible rows in matrix visual

tamerj1 — Wed, 15 Feb 2023 15:27:52 GMT

Hi Maieev

let's try something simple like

Count products =
COUNTROWS ( VALUES ( 'Sales Order Details'[Order Number] ) )

Re: Sum visible rows in matrix visual

Maieev — Thu, 16 Feb 2023 08:39:20 GMT

It's counting the number of orders correctly but not the number of products:

Re: Sum visible rows in matrix visual

tamerj1 — Thu, 16 Feb 2023 08:58:45 GMT

Maieev

please try

Count products =
COUNTROWS ( VALUES ( 'sales Order Details'[Product ID Description] ) )

Re: Sum visible rows in matrix visual

Maieev — Thu, 16 Feb 2023 09:20:09 GMT

Product ID Description field comes from a different table 'Product'[Product ID Description] and when I try to:

Countproduct = COUNTROWS(VALUES('Product'[Product ID Description]))

it's not working because it's direct query and probably is running through the whole underlying data.

Re: Sum visible rows in matrix visual

tamerj1 — Thu, 16 Feb 2023 09:46:01 GMT

Maieev

I meant to say

Count products =
COUNTROWS ( VALUES ( 'sales Order Details'[Product ID] ) )

I guess the Product ID exists in the Sales Order Details table.
Using 'Product'[Product ID Description] won't work unless you CROSSFILTER the relationship with Sales Order Details table to BOTH like

Count products =
COUNTROWS (
CALCULATETABLE (
VALUES ( 'Product'[Product ID Description] ),
CROSSFILTER ( 'sales Order Details'[Product ID], 'Product'[Product ID], BOTH )
)
)

Re: Sum visible rows in matrix visual

Maieev — Thu, 16 Feb 2023 10:04:26 GMT

Oh, getting closer.

= COUNTROWS(VALUES('Sales Order Details'[ItemDWHKey]))

there was a field in sales table for products but I can see it counts unique values probably? As for example here it shows 10 where should be 29.

Re: Sum visible rows in matrix visual

tamerj1 — Thu, 16 Feb 2023 10:40:24 GMT

Maieev
In this case we need to force additivity

Count products = SUMX ( SUMMARIZE ( 'Sales Order Details', 'Sales Structure'[Country], 'Customer Sold To'[Customer Sold To], 'Sales Order Details'[Order Number] ), COUNTROWS ( CALCULATETABLE ( VALUES ( 'Sales Order Details'[ItemDWHKey] ) ) ) )

Re: Sum visible rows in matrix visual

Maieev — Thu, 16 Feb 2023 11:10:35 GMT

tamerj1

Thank you so much for all the help!!! It works 🙂

Re: Sum visible rows in matrix visual

Maieev — Thu, 16 Feb 2023 11:18:36 GMT

tamerj1

got too excited, I assume it's minor one to fix
it works, but every line should have 1...

Re: Sum visible rows in matrix visual

tamerj1 — Thu, 16 Feb 2023 11:21:07 GMT

Maieev
Which column connects 'Product' to 'Sales Order Details'?

Re: Sum visible rows in matrix visual

Maieev — Thu, 16 Feb 2023 11:27:50 GMT

'Product'[ItemDWHKey] 1

'Sales Order Details'[ItemDWHKey] many

Re: Sum visible rows in matrix visual

tamerj1 — Thu, 16 Feb 2023 11:31:33 GMT

Maieev
Please try

Re: Sum visible rows in matrix visual

Maieev — Thu, 16 Feb 2023 11:39:09 GMT

Yes, exactly this is it. Thanks again!