Forum Discussion

ybyb23's avatar
ybyb23
Frequent Visitor
3 years ago
Solved

Generating dynamic rows and counting them

Hi all, 

 

I have been struggling with my dax function to generate for each row a value 1 then calculte the sum, however whenever I add another field in my table the total should increase but it stays the same. 

 

Is there a dynamic way to simply generate this 1 for each new row with the sum of all of them?

 

Here is what I am using, there are some cells blank as well. 

 

Product Idproduct NameMarketpriceCout
Tv-123Tvstore11
Tv-325Tvonline21
PS4-456PS4  1
PS4-247ps4 21
Total  54

Whenever I add other columns to this table as here below, I keep on seeing the 1 but the total stays the same 

 

Product Idproduct NameMarketsizePricecount
Tv-123tvonline100.51
Tv-123tvonline50.51
Tv-123tvonline811
Tv-123tvonline611

 

Can anyone please help me on that

 

thanks! 

DAX
DAX Calculation
DAX Commands and Tips
Help Request
Help required
Help with DAX
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    Hi ybyb23 ,

     

    There are two reasons which may cause numeric fields not automatically summed:

    1, The field data type is "this article" type.

    2, There is a modeling relationship.

    According to your description, I created a sample, and here is my solution.

    Create a column.

     

    count =
IF (
    COUNTROWS (
        FILTER (
            'Sheet1',
            'Sheet1'[Product ID] = EARLIER ( 'Sheet1'[Product ID] )
                && 'Sheet1'[Product Name] = EARLIER ( 'Sheet1'[Product Name] )
                && 'Sheet1'[Market] = EARLIER ( 'Sheet1'[Market] )
                && 'Sheet1'[price] = EARLIER ( 'Sheet1'[price] )
                && 'Sheet1'[size] = EARLIER ( Sheet1[size] )
        )
    ) > 0,
    "1",
    BLANK ()
)

     

    When you add rows to the table, you will get the final output.

     

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Souunds like you want to inspect your data model.  Same value in all rows usually points to a mis-picked column from the wrong side of the relationship.

    • ybyb23's avatar
      ybyb23
      Frequent Visitor
      3 years ago

      Hi lbendlin, 

      what I was trying to do is to see if the number of rows are correct to calculte a % based on another condition 🙂 

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Community Support
    3 years ago

    Hi ybyb23 ,

     

    There are two reasons which may cause numeric fields not automatically summed:

    1, The field data type is "this article" type.

    2, There is a modeling relationship.

    According to your description, I created a sample, and here is my solution.

    Create a column.

     

    count =
IF (
    COUNTROWS (
        FILTER (
            'Sheet1',
            'Sheet1'[Product ID] = EARLIER ( 'Sheet1'[Product ID] )
                && 'Sheet1'[Product Name] = EARLIER ( 'Sheet1'[Product Name] )
                && 'Sheet1'[Market] = EARLIER ( 'Sheet1'[Market] )
                && 'Sheet1'[price] = EARLIER ( 'Sheet1'[price] )
                && 'Sheet1'[size] = EARLIER ( Sheet1[size] )
        )
    ) > 0,
    "1",
    BLANK ()
)

     

    When you add rows to the table, you will get the final output.

     

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ybyb23's avatar
      ybyb23
      Frequent Visitor
      3 years ago

      Hi v-xiaosun-msft, 

       

      thank you very much for your support. It worked perfectly! 

       

      Also I have approched it differently with the summerize function  I have added all the fields name to count and it worked.