cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ybyb23
Frequent Visitor

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! 

1 ACCEPTED SOLUTION
v-xiaosun-msft
Community Support
Community Support

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

 

vxiaosunmsft_0-1664186132732.png

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

vxiaosunmsft_1-1664186132739.pngvxiaosunmsft_2-1664186132740.png

 

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.

View solution in original post

4 REPLIES 4
v-xiaosun-msft
Community Support
Community Support

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

 

vxiaosunmsft_0-1664186132732.png

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

vxiaosunmsft_1-1664186132739.pngvxiaosunmsft_2-1664186132740.png

 

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.

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. 

 

lbendlin
Super User
Super User

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.

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 🙂 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors