cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
labuser1235
Helper IV
Helper IV

Cumulative count with Blanks

Hi All,

 

I want the cumulative values where I was want to include blanks in the count. I had tried && where it counts blanks as well yet it doesnt work. As per the countrows it should start from 8, however it is starting from 6 as there is no date value for in date sold. 

I want it to include the blanks in the count. 

I have two tables Table1 and Date table.

Reverse cumulative wil help me to understand pending till date.

Can someone please help with with this.

 

Input: Table1

Screenshot 2020-07-16 at 5.42.52 PM.png

Screenshot 2020-07-16 at 5.11.53 PM.pngScreenshot 2020-07-16 at 5.36.48 PM.png

2 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

Hi @labuser1235 ,

 

Try this:

Count =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date Sold] >= MIN ( DateTable[Date] ) )
)
    + CALCULATE ( COUNTBLANK ( Table1[Date Sold] ), ALL ( Table1 ) )

 

 

Best Regards,

Icey

 

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

Thank you so much @Icey . I have tried another way too which worked as well. 

 

Count =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date Sold] >= MIN ( DateTable[Date] ) || ISBLANK(TABLE1[DATE Sold]) )
)

 

View solution in original post

9 REPLIES 9
Icey
Community Support
Community Support

Hi @labuser1235 ,

 

Try this:

Count =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date Sold] >= MIN ( DateTable[Date] ) )
)
    + CALCULATE ( COUNTBLANK ( Table1[Date Sold] ), ALL ( Table1 ) )

 

 

Best Regards,

Icey

 

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

Thank you so much @Icey . I have tried another way too which worked as well. 

 

Count =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Date Sold] >= MIN ( DateTable[Date] ) || ISBLANK(TABLE1[DATE Sold]) )
)

 

View solution in original post

hstgeorge
Helper III
Helper III

Also you could do the DistincCount function on the Table(Name) field and that should give you 8 and then put the date on the graph. 

hstgeorge
Helper III
Helper III

 Would it be possible for you to use and calculated column to replace the blanks fields with something else to indicate "NO DATE" and "INCOMPLETE".  That would populate the fields with a value and you wouldn't have to deal wtih the blanks anymore. Another option would be the ISBLANK(Value) which checks whether it true/false for blank and then you can build that into the functions below.  A couple different options.  If you think those would work, I can send over some potential calcs for you.

@hstgeorge Hi Thanks for you response, I cannot replace the columns rather I would want to use measure to count the values. If possible I have attached the PBI link

Please take a look if you count based on blanks in the measure. 

amitchandak
Super User
Super User

@labuser1235 ,Can you share sample data and sample output in table format? need to try on power Bi

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Sure @amitchandak . 

 

Here is the PBI link

 

Thanks.

@labuser1235 , see if this file can help

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak Hi Amit, Thanks for your repsonse. It still shows cummulative however, i want reverse cummulative with blanks in the count too. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.