Forum Discussion
Cumulative count by month
Attempting to caluate a % of grand total trend for a categorical variable, finding it is understated, and sounds like I need a new data table with summaries to create a new variable.
Reading through a bunch posts and pirating some code I found but getting an expression error.
Bid Bond Summary Counts = CALCULATE(COUNT('Bid Bond Results'[final_score]),FILTER(ALLSELECTED('Bid Bond Results'),'Bid Bond Results'[execution_date].[month] <= MAX('Bid Bond Results'[execution_date].[month])))
Hi Anonymous
If you want the total of percentage to be 100% for every date, you can try the following measure
Measure = DIVIDE(COUNT('Table'[final_score]),CALCULATE(COUNT('Table'[final_score]),ALL('Table'[final_score])))Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
6 Replies
- SpartaBI
Community Champion
Anonymous if you mean running total for the selected dates in your visual try this:
(You should always have a proper date table and not the automated one which you use, but if I understood your need, this will work anyway):Bid Bond Summary Counts = CALCULATE ( COUNT ( 'Bid Bond Results'[final_score] ), FILTER ( ALLSELECTED ( 'Bid Bond Results' ), 'Bid Bond Results'[execution_date] <= MAX ( 'Bid Bond Results'[execution_date]) ) ) The above code will not run, I get a not valid table expression error, but no coding errors squiggly lines etc. My existing transactional data includes an 'execution date' and classification variable 'final score'. Ultimately I need a summary table by date with running counts so that I can build trends lines by execution date. I.e. count of classification 1/count of all classifcations for a given month to show change over time.
- SpartaBI
Anonymous strange. This code should work. I'm missing info about your data. Maybe you can share a sample data with all the columns in a table snipet and the table you want to achive as a visual.
I'd like the total to add to 100% for any date for the (3) clasifcations associated with 'final_score', I'm using transactional data so there maybe be 1,000 unique observations (transactions) per day and there are (3) distinct classifcations within final score probaly 50 possible variables so just throwing a few rows here.
id final_score execution date 1 red 5/1/2022 2 blue 5/1/2022 3 green 5/1/2022 4 red 4/30/2022
5 blue 4/30/2022 
v-jingzhangCommunity Support
Hi Anonymous
If you want the total of percentage to be 100% for every date, you can try the following measure
Measure = DIVIDE(COUNT('Table'[final_score]),CALCULATE(COUNT('Table'[final_score]),ALL('Table'[final_score])))Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
- SpartaBI
Anonymous sorry, still don't understand your need. Let's wait for other community members to join this thread.
