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 

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

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

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

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