User Profile
MalyMajo
Frequent Visitor
Joined 5 years ago
User Widgets
Contributions
AD group and resource hierarchy
Hello all, I have tabular data, which includes list of resources and the groups to which they belong. Is it possible to format the data somehow to always show the structure when a resources is filtered? Index Level Value 1 Level1 ProjectName_All 2 Level2 ProjectName_Team1 3 Level3 ProjectName_Team1_Offshore 4 Level4 peter.pan 5 Level3 ProjectName_Team1_Onshore 6 Level4 peter.parker 7 Level4 tony.hawk 8 Level2 ProjectName_Team2 9 Level3 ProjectName_Team2_Offshore 10 Level4 kendrick.lamar 11 Level4 tony.stark 12 Level4 slim.shady 13 Level4 cristiano.ronaldo 14 Level4 lionel.messi 15 Level4 alexander.ovechkin 16 Level3 ProjectName_Team2_Onshore 17 Level4 tony.hawk So for example if I would filter tony.hawk, I would like to see following table: Index Level Value 1 Level1 ProjectName_All 2 Level2 ProjectName_Team1 5 Level3 ProjectName_Team1_Onshore 7 Level4 tony.hawk 8 Level2 ProjectName_Team2 16 Level3 ProjectName_Team2_Onshore 17 Level4 tony.hawk Please note, that resources can be in multiple teams. Additionally, resources does not necessary need to always be on "Level4". It can be anything between Level3 and Level6. Naming convention of groups is always same - in each case it starts with "ProjectName".Total with SUM instead of MAX
I have an issue in my data model with calculation of totals. My inputs are following: - burnt effort per resource and day (in hours) - scheduling report (roster with resource details) - rate card with different loaded cost rate (LCR) per each activity type and country - exchange rate for each currency on monthly level - mapping table of countries and their currencies I am trying to get the cost calculated in USD, considering that the rates differ every month. The measure calculates properly on resource level, but not when summing on month level. I understand, that the reason is, that the measure calculates MAX of the rate and when all the monthly values are summed, only the highest exchange rate is considered. However, I cannot figure out how to change the measure to SUM the calculations on total level instead of calculating it separately. I have tried to follow the currency conversion example from DAX Patterns (link). Costs USD:=VAR AggregatedCostsInCurrency = ADDCOLUMNS( SUMMARIZE( Actuals, 'Calendar'[Year_Month], Actuals[Name] ), "@CostsAmountInCurrency",[Costs (internal)], "@Rate", CALCULATE( MAX(ExchangeRates[Rate])) ) VAR CostUSD = SUMX ( AggregatedCostsInCurrency, DIVIDE([@CostsAmountInCurrency],[@Rate]) ) RETURN CostUSD Here is the diagram view of my simplified data model: Any help will be appreciated.
