Forum Discussion

Maieev's avatar
Maieev
Icon for Resolver I rankResolver I
3 years ago
Solved

Sum visible rows in matrix visual

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 diffe...
"DAX"
COUNTROWS
Matrix Visual
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
    tamerj1
    3 years ago

    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 )
        )
    )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
Maieev
Icon for Resolver I rankResolver I
3 years ago

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Maieev 

    let's try something simple like

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

    • Maieev's avatar
      Maieev
      Icon for Resolver I rankResolver I
      3 years ago

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

       

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Maieev 

        Ok

        please try

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