Total count per month independent of slicers

[Anonymous]

Dear Datanauts,

I have data that consists of one of six categories applied to hundreds of records, each with a date associated with the record. What I'm trying to do is look at the trends of the six categories over time. To do this, I need to normalise the data for each time period that I'm interested in looking at (e.g. monthly). I'm finding this exceedingly difficult in BI.

What I've tried so far is to count the rows of the table using COUNTROWS (which will change depending on the filters applied to the visual), then to DIVIDE the COUNTROWS (i.e. numerator) by a static COUNTROWS that does not change with filters or slicers (i.e. the demoninator).

Nothing I've tried so far has given me what I need. The closest I've come is using:

Total Monthly Count = CALCULATE(COUNTROWS(Table1), ALLEXCEPT('DateTable','DateTable'[Date].[Month]))

The table below shows what this script gives. What I need is for all the values in the table to be the same as the 'Total' for each respective month. 

I initially tried using:

Total Monthly Count = COUNTROWS(ALLSELECTED(Table1))

But this only gives the total for the table, not the respective months. Is there a way to get each entry in the table to be the same as the monthly total?

[Anonymous · Accepted solution]

I found a solution, but it's less than ideal.

Firstly, I created a Calculated Column using the following formula so that each entry in the data table has the total count for that month.

Monthly Total = CALCULATE(COUNTROWS(Table1), FILTER(Table1, Table1[Created Date].[Month] = EARLIER(Table1[Created Date].[Month])))

Next, I created a new measure that provides the percentage of the monthly total using the median of the above calculated column. This forces BI to always use the monthly total regardless of what slicer is selected.

% = DIVIDE(COUNTROWS(Table1), MEDIAN(Table1[Monthly Total]), 0)

This will only work when monthly data is used, so it's not a very eligant method. It's a shame that (as far as I'm aware) BI can't make certain measures independent of slicers and filters.

[Ashish_Mathur]

Hi,

Show some data and the expected output in a Table format.  Once the figures in the Table are correct, any visual can be prepared.

[Zubair_Muhammad]

@Anonymous 

try following

Total Monthly Count =
CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1[Avoidability[Level 2] ) )

[Anonymous]

Thank you for your reply and terrific suggestion Zubair_Muhammad.

Total Monthly Count =
CALCULATE ( COUNTROWS ( Table1 ), ALL ( Table1[Avoidability[Level 2] ) )

---

The above formula works great until I apply a slicer. Do you know of a way to ensure that slicers don't influence the calculation?

Many thanks 🙂