How to sum over a time level

Question

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

sikimimi · Answer

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

Aswin Raj D · Answer

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 · Answer

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