Forum Discussion

robertosangi's avatar
robertosangi
Helper I
4 years ago
Solved

COUNT and difference

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      robertosangi
      Helper I
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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