Sum visible rows in matrix visual

[Maieev]

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.

[tamerj1 · Accepted solution]

@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] ) ) )
)

[tamerj1]

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. 

[Maieev]

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.

[tamerj1]

Hi @Maieev 

let's try something simple like

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

[Maieev]

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

[tamerj1]

@Maieev 

Ok

please try

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

[Maieev]

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.

[tamerj1]

@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 )
)
)

[Maieev]

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.

[tamerj1 · Accepted solution]

@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] ) ) )
)

[Maieev]

@tamerj1 

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

[tamerj1]

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

[Maieev]

'Product'[ItemDWHKey] 1

to

'Sales Order Details'[ItemDWHKey] many

[tamerj1 · Accepted solution]

@Maieev 
Please try

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

[Maieev]

Yes, exactly this is it. Thanks again!

[Maieev]

@tamerj1 

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