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

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

        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.