Subtotal for each day with DAX

2
votes

I'm trying to show the days when some people were present. For this I created a column called 'Presence' and put all values ​​equal to 'P'. After that I created a DAX statement to count 1 once a day that person, and then with that check if there is at least one record for it on that day.

DAX:

Presenca_medida = CALCULATE(DISTINCTCOUNT('Base 
Atendimento'[DATA_INICIO_CONCAT]);FILTER('Base Atendimento';
'Base Atendimento'[Presenca] = "P"))

This worked very well.

But I'm having a problem, because I'm not able to add the total per column, just by lines.

As picture:

enter image description here

As we can see he is adding each line correctly. But the column it only replicates the same value (not sum).

Any idea?

Example of table and colluns:

enter image description here

Add

When i try with idChamado the total appear, like that:

enter image description here

but the problem it's taht if use idChamado, i can't count once a day..

2
powerbidax
Try COUNT or COUNTA instead of DISTINCTCOUNT maybe? – Alexis Olson
i've tried but i didnt get just one register for each day – Clayton Tosatti
What does your source data table look like? – Alexis Olson
I edited the question with the image – Clayton Tosatti
Try counting idChamado or dataInicio instead of DATA_INICIO_CONCAT. – Alexis Olson

2 Answers

3
votes

Using the same sample data @vestland provided,

Name, DATA_INICIO_CONCAT, Presenca
Tayna, 21.09.2018, P
Tayna, 24.09.2018, P
Tayna, 25.09.2018, 
Tayna, 26.09.2018, 
Tayna, 27.09.2018, 
Tamires, 21.09.2018, 
Tamires, 24.09.2018, P
Tamires, 25.09.2018, P
Tamires, 26.09.2018, 
Tamires, 27.09.2018, 
Surya, 21.09.2018, 
Surya, 24.09.2018, 
Surya, 25.09.2018, P
Surya, 26.09.2018, P
Surya, 27.09.2018, P

I can match the same result with a simple measure:

Measure = COUNTX('Table1', IF('Table1'[Presenca] = "P", 1))

Matrix Visual

1
votes

You asked for a DAX approach. But do you really need it?

Using a Matrix Visualization and a few steps in the Power Query Editor will give you the following with row and column totals:

enter image description here

The details:

I've sampled some of your data to mimic your datastructure:

Name, DATA_INICIO_CONCAT, Presenca
Tayna, 21.09.2018, P
Tayna, 24.09.2018, P
Tayna, 25.09.2018, 
Tayna, 26.09.2018, 
Tayna, 27.09.2018, 
Tamires, 21.09.2018, 
Tamires, 24.09.2018, P
Tamires, 25.09.2018, P
Tamires, 26.09.2018, 
Tamires, 27.09.2018, 
Surya, 21.09.2018, 
Surya, 24.09.2018, 
Surya, 25.09.2018, P
Surya, 26.09.2018, P
Surya, 27.09.2018, P

Import that data, split by ',' use Add Column > Conditional Column and set it up like this:

enter image description here

Now you can use a Matrix Visualization. At first, you will get only 1 in all row and column totals because your Custom column representing 1 or null is set to show First value. Change that to Count instead:

enter image description here

And you'll get this:

enter image description here