How to sum over a time level

[sikimimi]

Hello all, 

I am importing data from an external source (API) and creating a dashboard from it. I created a time dimension (Aggregate by days).

Now I want to create a calculated measure, that would give a value of 3.25/8 in time level (under the PI level) and the sum over the day in PI level. 

So for example for PI level it would be 6.5/8 and for the days under it, it would be 3.25/8. 

I was trying this formula but it does not work : 

CASE WHEN
[Time].[Day].CurrentHierarchyMember.Level.Name = "Day"
THEN
SUM(
VisibleRowsSet(),
3.25/8
)
END

Thank you in advance for you help :) 

[Aswin Raj D]

Dear @sikimimi ,

Welcome to the Atlassian Community!

Could you please try to modify your formula to handle both the day level and the PI level differently. Something like below.

CASE
WHEN [Time].[Day].CurrentHierarchyMember.Level.Name = "Day" THEN
3.25 / 8
WHEN [Time].[Day].CurrentHierarchyMember.Level.Name = "PI" THEN
SUM(
[Time].[Day].CurrentHierarchyMember.Children,
3.25 / 8
)
END

[sikimimi]

Thank you for your answer :) 

When I do your formula, the days are not under their corresponding PI anymore hence the sum is not displayed at PI level. 

Do you know how to fix that ? Thank you so much :) 

[Aswin Raj D]

Dear @sikimimi ,

The formula should handle both day and PI levels appropriately while maintaining the hierarchy structure:

CASE
WHEN [Time].[Day].CurrentHierarchyMember.Level.Name = "Day" THEN
3.25 / 8
WHEN [Time].[Day].CurrentHierarchyMember.Level.Name = "PI" THEN
SUM(
Filter(
[Time].[Day].CurrentHierarchyMember.Children,
[Time].[Day].CurrentHierarchyMember.Children.CurrentMember.Level.Name = "Day"
),
3.25 / 8
)
END

If the hierarchy still appears incorrectly, you may need to ensure that your time dimension is correctly configured in your BI tool. Verify that the parent-child relationships are properly defined.

If these steps don't resolve the issue, consider providing additional details or screenshots of your dimension configuration, which could help in providing more tailored advice.

[sikimimi]

Hello, 

I created individual members in the Time Dimension by aggregating the date into PIs. (Screenshot above). So I did not set any hierarchy between the members.

Thanks for your help :)