Count the numbers in a column depending on other columns

[dodolabs]

Hi everyone,

I'm trying to create a formula but i need some help.

I have a table with a list of support hours, support categories and dates.

Something like this:

Support hour     support category      date
11                      Type 1                       2020.01.19
15                      Type 2                       2020.02.05
6                        Type 2                       2019.08.06
9                        Type 1                       2020.01.08
4                        Type 1                       2019.11.22

My goal is to count the support hours of different support category for each month:
Type 1:
2019.11: 4
2020.01: 20

Type 2:
2019.08: 6
2020.02: 15


I've tried measures but without success.
Thank you all

[V-lianl-msft]

Hi @dodolabs ,

If your calendar is data in date format, you may not need to use DAX, just add a category slicer.

You can also format the year and month.

Year_month = FORMAT(Sheet4[date],"YYYY.MM")

Here is the sample pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @dodolabs 

try a measure

Measure= CALCULATE( SUM(Table[Support hour]), ALLEXCEPT(Table, Table[support category], Table[date]) )

[dodolabs]

Hi!

Thanks, but how can I get the sum of each support category and month?

I think I need measure for every month and category.

The category column can have more than two categories, and of course there can be more dates in the date column

for example:
Category 1:
2019.11 (sum): 4
2020.01 (sum): 20

 Category 2:
2019.08 (sum): 6
2020.02 (sum): 15

Category 3:

2019.01.04 (sum): 27

2019.05.10 (sum): 67

2020.01.03 (sum): 6

2020.02.27 (sum): 2

[V-lianl-msft · Accepted solution]

Hi @dodolabs ,

Please try the dax below:

Measure = CALCULATE(SUM(Sheet4[Support hour]),ALLEXCEPT(Sheet4,Sheet4[support category],Sheet4[Year_month]))

The sample pbix has updated.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dodolabs]

Thank you for your help!