Forum Discussion

Matpbi's avatar
Matpbi
Frequent Visitor
1 year 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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year 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.

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year 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
    1 year 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
      1 year 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.

      • Matpbi's avatar
        Matpbi
        Frequent Visitor
        1 year ago

        Hi, 

        If I make a matrix from the measure below, I receive the situation below in the excel (values are an example).

        Problem: later, when I make my final matrix with the final measure, my individual values are correct but the total of my row values aren't correct, due to the fact that, for the measure "wages" the total of my row values are exactly the same as the individuel values for 1 row as you can see (in my example: 1200 for the first month). So when I later add my "Wages" to the "sumwages", I lose 2 * 1200 for my total-column values.
        How do I change my measure "Wages" in order to receive the sum of each row in my total-column? (In my example: 3600 for the first month)? Thanks in advance!

         

         

        wages =
    ROUND (
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Wage] ),
                ALLSELECTED ( 'Table' ),
                'Table'[Month] IN VALUES ( 'Table'[Month] ),
                'Table'[Department] = "Guarantee"
            ),
            countcity
        ),
        2
    )

         

        Month/CityBRUSSELSGENTANTWERPTOTAL
        11200120012001200
        21600160016001200