Dynamic column value change (rows) according to date slicer to calculate the count,

[ksahil16]

Hello,

I have a table which looks something like this:

Category	Importance	weight	Date
A	low	2	2016
A	low	2	2017
A	v .low	1	2018
A	medium	3	2020
B	low	2	2019
B	medium	3	2020
C	high	4	2017
C	low	2	2020
D	v  high	5	2021

I want to create a column which creates the count for number of rows per Category per Importance.

Category	Importance	weight	Date	Count
A	low	2	2016	2
A	low	2	2017	2
A	v .low	1	2018	1
A	medium	3	2020	1
B	low	2	2017	2
B	low	2	2020	2
C	high	4	2017	2
C	high	4	2020	2
D	v  high	5	2021	1

This is something I can get using : 

Count =

CALCULATE(

    COUNTROWS(Table),

    ALLEXCEPT(Table, Table[Category], Table[Importance])

)

but if I select the slicer for Date from 2018, the numbers should change and the table should become like this:

Category	Importance	weight	Date	Count
A	v .low	1	2018	1
A	medium	3	2020	1
B	low	2	2020	1
C	high	4	2020	1
D	v  high	5	2021	1

as you can see we only have data from 2018 and also the Count has changed, this is something that I am not able to achieve for calculated columns. The Count still remains the (B, low) as 2, as it was calculated on the entire column in the previous formula. How can i make the Count column dynamic according to the slicer, which changes the count of rows.

NOTE: I have also tried to this in measure, with success, also after calculating the desired column and carrying on with all the after calculations to get myself entually 3 points in my measure column which I would like to plot. but when I try to plot it, it gives me directly the aggregate, so I am opted back to calculated column.

[parry2k · Accepted solution]

@ksahil16 I'm still not clear what you are trying to achieve but did you use category column on the legend? I'm sure I'm missing something here but don't know what that is?

[ksahil16]

Hello @parry2k , How could I miss this, putting Category column in the Legend actually solved it. I knew I was doing everything right in terms of calculations but was missing this step. 

Thank you for the help and reading through my post patiently.

[parry2k]

@ksahil16 not sure why to create a calculated column, the best practice should be to create a measure.

[ksahil16]

Hello @parry2k ,

I did create Measure for the calculation, to give an example:

Count = CALCULATE(COUNT('Table'[Category]))

to follow that I also create one more column in form of "Measures", Count_x_weight

Category	Importance	weight	Date	Count	Count_x_weight
A	v .low	1	2018	1	1
A	medium	3	2020	1	3
B	low	2	2020	1	2
C	high	4	2020	1	4
D	v  high	5	2021	1	5

Considering the same thing, I created two more Measures - Count_sum (sum of the Count column), Count_x_weight_sum (sum of the Count_x_weight).

My final measure was Point_to_plot - (Count_x_weight_sum) / Weight_sum

Category	Weight_sum	Count_x_weight_sum	Point_to_plot
A	2	4	2
B	1	2	2
C	1	4	4
D	1	5	5

Here I recieve the final Measure I want to plot, but when I try to plot this on a scatter plot as my X- axis I only get one point, which is the aggregation 3.25. I would like to have 4 point representing 4 categories, but as I was not able to visualize that using Measure i thought of doing it in calculated columns.

How do I visualize the Point_to_plot for every Category?

NOTE: Please note that when I apply the Date slicer the number of rows change and every calculation changes.