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

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

15 Replies

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