How to sum over a time level

sikimimi May 24, 2024

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).

 

Screenshot 2024-05-24 095743.png

 

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. 

Screenshot 2024-05-24 095917.png

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 :) 

1 answer

0 votes
Aswin Raj D
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 24, 2024

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 May 24, 2024

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. 

Screenshot 2024-05-24 111747.png

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

Aswin Raj D
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 24, 2024

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 May 24, 2024

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 :) 

 

Suggest an answer

Log in or Sign up to answer