Forum Discussion

Matpbi's avatar
Matpbi
Frequent Visitor
2 years ago
Solved

Split department values in cities

Hi,

 

How can I split the wages / worked hours of the guarantee-department over the 3 cities?


I want to make a measure with the sum of the "wages" for the 3 departments and divide it through the "worked hours" from the workshop-department (because it's the only productive department), in order to make a matrix with cities (column) - months (row)- measure (value).  I'm able to make that sum for the workshop -and backofficedepartment but not for the guarantee-department, because it's not related to a city. The wages (and worked hours) for the guarantee department should be split through the 3 cities and I don't know how to do this in pbi...

 

Thanks in advance.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution bhanu_gautam offered and i want to offer some more infomation foe user to refer to.

    hello Matpbi 

    You can create a measure.

    MEASURE =
//calculate the city counts in each month
VAR countcity =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[City] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Month] IN VALUES ( 'Table'[Month] ),
        'Table'[City] <> BLANK ()
    ) 
//calculate the wages of Guarantee that dispatch to each city
VAR wages =
    ROUND (
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Wage] ),
                ALLSELECTED ( 'Table' ),
                'Table'[Month] IN VALUES ( 'Table'[Month] ),
                'Table'[Department] = "Guarantee"
            ),
            countcity
        ),
        2
    ) 
//calculate the hours of Guarantee that dispatch to each city
VAR hours =
    ROUND (
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Worked hours] ),
                ALLSELECTED ( 'Table' ),
                'Table'[Month] IN VALUES ( 'Table'[Month] ),
                'Table'[Department] = "Guarantee"
            ),
            countcity
        ),
        2
    ) 
//calculate the count of department of each city in each month
VAR counts =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Department] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Month] IN VALUES ( 'Table'[Month] ),
        'Table'[City] IN VALUES ( 'Table'[City] )
    ) 
//calculate  the hours of Guarantee that dispatch to each department of each city
VAR averagehours =
    ROUND ( DIVIDE ( hours, counts ), 2 ) 
//calculate the sumwages
VAR sumwages =
    CALCULATE (
        SUM ( 'Table'[Wage] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Month] IN VALUES ( 'Table'[Month] ),
        'Table'[City] IN VALUES ( 'Table'[City] )
    ) + wages 
//calculate the sumhours of workshop in each city in each month.
VAR workshophours =
    CALCULATE (
        SUM ( 'Table'[Worked hours] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Month] IN VALUES ( 'Table'[Month] ),
        'Table'[City] IN VALUES ( 'Table'[City] ),
        'Table'[Department] = "workshop"
    ) + averagehours 
//divide
RETURN
    IF ( MAX ( 'Table'[City] ) <> BLANK (), DIVIDE ( sumwages, workshophours ) )

    Output

     

    Best Regards!

    Yolo Zhu

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

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Matpbi ,Create a table that defines the allocation of the guarantee-department wages 

    CityAllocation =
       DATATABLE (
           "City", STRING,
           "Allocation", DOUBLE,
           {
               {"City1", 0.33},
               {"City2", 0.33},
               {"City3", 0.34}
           }
       )
     
    Then Create measures to calculate the total wages and worked hours for the guarantee department.
    TotalGuaranteeWages = CALCULATE(SUM(Employees[Wages]), Employees[Department] = "Guarantee")
       TotalGuaranteeWorkedHours = CALCULATE(SUM(Employees[WorkedHours]), Employees[Department] = "Guarantee")
     
    Create measures to allocate the guarantee department's wages and worked hours to each city.

    DAX
    AllocatedGuaranteeWages =
    SUMX(
    CityAllocation,
    [TotalGuaranteeWages] * CityAllocation[Allocation]
    )

    AllocatedGuaranteeWorkedHours =
    SUMX(
    CityAllocation,
    [TotalGuaranteeWorkedHours] * CityAllocation[Allocation]
    )

     

    Then combine all departments data

    DAX
    TotalWages =
    SUM(Employees[Wages]) + [AllocatedGuaranteeWages]

    TotalWorkedHours =
    SUM(Employees[WorkedHours]) + [AllocatedGuaranteeWorkedHours]

     

    Create a final measure to divide total wages by the worked hours

    FinalMeasure =
       DIVIDE(
           [TotalWages],
           CALCULATE(SUM(Employees[WorkedHours]), Employees[Department] = "Workshop")
       )
     
    Add a matrix visualization to your report.
    Set the rows to months, columns to cities, and values to the FinalMeasure.
    • Matpbi's avatar
      Matpbi
      Frequent Visitor
      2 years ago

      Hi,

       

      Thanks for the answer!

      I did everything as you said but the result I get is without the wages of the guarantee-department....

      Do I have to make an active one-to-many relationship between city-column in "CityAllocation" and the city-column in "Employees"?
      I think I have to do this but if I do so, i don't get a result in my "AllocatedGuaranteeWages", it's blanc (if i make a simple matrix to see the result of this measure). Therefore later, the result of this measure is 0 for my cities and it doesn't have an infuence.

       

      Thanks in advance.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,

        Thanks for the solution bhanu_gautam offered and i want to offer some more infomation foe user to refer to.

        hello Matpbi 

        You can create a measure.

        MEASURE =
//calculate the city counts in each month
VAR countcity =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[City] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Month] IN VALUES ( 'Table'[Month] ),
        'Table'[City] <> BLANK ()
    ) 
//calculate the wages of Guarantee that dispatch to each city
VAR wages =
    ROUND (
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Wage] ),
                ALLSELECTED ( 'Table' ),
                'Table'[Month] IN VALUES ( 'Table'[Month] ),
                'Table'[Department] = "Guarantee"
            ),
            countcity
        ),
        2
    ) 
//calculate the hours of Guarantee that dispatch to each city
VAR hours =
    ROUND (
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Worked hours] ),
                ALLSELECTED ( 'Table' ),
                'Table'[Month] IN VALUES ( 'Table'[Month] ),
                'Table'[Department] = "Guarantee"
            ),
            countcity
        ),
        2
    ) 
//calculate the count of department of each city in each month
VAR counts =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Department] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Month] IN VALUES ( 'Table'[Month] ),
        'Table'[City] IN VALUES ( 'Table'[City] )
    ) 
//calculate  the hours of Guarantee that dispatch to each department of each city
VAR averagehours =
    ROUND ( DIVIDE ( hours, counts ), 2 ) 
//calculate the sumwages
VAR sumwages =
    CALCULATE (
        SUM ( 'Table'[Wage] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Month] IN VALUES ( 'Table'[Month] ),
        'Table'[City] IN VALUES ( 'Table'[City] )
    ) + wages 
//calculate the sumhours of workshop in each city in each month.
VAR workshophours =
    CALCULATE (
        SUM ( 'Table'[Worked hours] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Month] IN VALUES ( 'Table'[Month] ),
        'Table'[City] IN VALUES ( 'Table'[City] ),
        'Table'[Department] = "workshop"
    ) + averagehours 
//divide
RETURN
    IF ( MAX ( 'Table'[City] ) <> BLANK (), DIVIDE ( sumwages, workshophours ) )

        Output

         

        Best Regards!

        Yolo Zhu

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