Forum Discussion
Helper ITransfers In Count in different hierachies
Hi everyone,
I have to figure out a way to calculate the Transfers In Count, which is on the lowest level of the hierachy just the SUM(Transfer Count).
The challenge is that the transfers into a unit is not a transfer into the department if it is from another unit in that department. Likewise, all units and departments were transfers happen is within the same company, so therefore the Transfers In Count on the company level is zero.
I have tried to visualize the challenge here:
Any help with this would be very appreciated. I have not done anything close to this before, but think one might use selectedvalue to look for the row context (company, department, unit) and then if (company=fromcompany, 1, 0) and remove 1, but do the same for all levels, but not sure how to progress from here.
Best regards,
Ole
It worked.
1)
IFunit = IF(Unit from = Unit to), 1, 0)
IFdepartment =IF(Department from = Department to, 1, 0)
IFcompany = IF(Company from = Company to, 1, 0)
2)
Transferred In Units = CALCULATE(SUM(transfers), IFunit=0)
Transferred In Department = CALCULATE(SUM(transfers), IFdepartment=0)
Transferred In Company = CALCULATE(SUM(transfers), IFcompany=0)
3
Transferred In total = IF(ISFILTERED(unit), Transferred in units, IF(ISFILTERED(department), Transferred in department, IF(ISFILTERED(company), Transferred in Company)))
This will give the correct result in one column for all the hierarchies.
2 Replies
hr_tetraThink I might be on to a solution.
1) Add IF calculated column for each hierarchy level where to unit = from unit is one or otherwise zero, then I will filter out the 1 values in the next step. Do this also for department and company.
2) add a sum measure for unit, department and company where the the IF calculated column above is used to filter out the 1 values.
This works well so far. No I only need to combine all the measures into one that will work in a matrix with the different levels, and I think ISFILTERED can be used for this using this method: https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/
- hr_tetra
It worked.
1)
IFunit = IF(Unit from = Unit to), 1, 0)
IFdepartment =IF(Department from = Department to, 1, 0)
IFcompany = IF(Company from = Company to, 1, 0)
2)
Transferred In Units = CALCULATE(SUM(transfers), IFunit=0)
Transferred In Department = CALCULATE(SUM(transfers), IFdepartment=0)
Transferred In Company = CALCULATE(SUM(transfers), IFcompany=0)
3
Transferred In total = IF(ISFILTERED(unit), Transferred in units, IF(ISFILTERED(department), Transferred in department, IF(ISFILTERED(company), Transferred in Company)))
This will give the correct result in one column for all the hierarchies.