COUNT and difference

[robertosangi]

I want to reproduce this situation in PowerBi.

I have three different tables named: Wn-1, Wn, Person

I wanted to know how much work did each person inside the table Person between the two weeks. 

Practically I need firstly to count in Wn-1 how many cases are in charge for each person. After this i need to do the same for the Wn table and finally I need a difference beetween them.

I'm not able to do so. I'm trying to do it in Data context in PowerBi

Please help

Thanks

[amitchandak]

@robertosangi , Join both of them with person Table

Then

wn -1 cases =sum( 'Wn-1'[Cases#])

wn cases =sum( 'Wn'[Cases#])

diff = [wn -1 cases] -[wn cases]

Use common person table to display data

[robertosangi]

@amitchandak I think is not easy as you said. I need to firstòy construct Columns "I" and "J" dynamically for each person. How I could do that? That's the main question.

I mean, for each row of the Person Table I need to have only the count of the relative amount of cases.

Just to be more clear.

Let's take Person A and B example.

In Wn-1 A had 1 case, B had 9 cases
In Wn A had 1 case again, B had 3 cases

I want in total 0 for A and -6 for B which means that A didn't work and B solved 6 cases respect to the previous week. 


Building the columns as you said I will have the total amount of Cases and not divided by person

[Anonymous · Accepted solution]

Hi @robertosangi ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create relationships between Wn, Wn-1 and Person table base on the field Person just as below screenshot

2. Create two measures as below to get the count of cases in Wn and Wn-1

Wn = COUNT('Wn'[#Case])

Wn-1 = COUNT('Wn-1'[#Case])

3. Create a measure as below to get the difference between Wn and Wn-1

Difference = [Wn]-[Wn-1]

4. Create a table visual as below screenshot

Best Regards

[robertosangi]

Thank you. This is what I meant!