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

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.
 

 

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

    tamerj1 

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

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

    Yes, exactly this is it. Thanks again!

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

    tamerj1 

     

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

     

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

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

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

    'Product'[ItemDWHKey] 1

    to

    'Sales Order Details'[ItemDWHKey] many