Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Toftaf
Frequent Visitor

Weekly sum of daily averages

Hello,

I read many posts about that subject and out of them I couldn't find a solution that works for me.

My need is the following.

In the table below I need to have the average GT2 per subtask or task for the data to be correct on these lines.

And in the different aggregation level (date, week, month) I need to have a sum of the average GT2 per subtask, task.

And I don't find how to do that.

 

Additionnal info:

GT2 = GT3 = theoretical day shift duration

When I used average instead of sum with GT3 data the task, subtask and date values are correct but then the week and month are not.

 

Toftaf_0-1709204454163.png

 

example of expected result:

For the 11th of january:

 Somme de Temps passéSomme de GT2
Jeudi 11 janvier 20249,58,00
AB5,008,00
DED2,008,00
DEL3,008,00
HO2,008,00
PR2,508,00

 

But for the entire week I need to see:

 

 Somme de Temps passéSomme de GT2
Week 2 22,5022,50

 

 

Data sample:

N° MoisN° SemaineDateTaskSubTaskTemps PasséGT2 (=GT3)
1210/01/2024 00:00HR 18
1210/01/2024 00:00PR 78
1211/01/2024 00:00ABDED28
1211/01/2024 00:00ABDEL38
1211/01/2024 00:00HO 28
1211/01/2024 00:00PR 2,58
1212/01/2024 00:00HO 0,176,5
1212/01/2024 00:00PR 4,836,5

 

 

Thanks for your help,

Toftaf.

 

2 REPLIES 2
Toftaf
Frequent Visitor

hello @amitchandak ,

Thanks for your answer.

I tried your solution without success. I also tried to investigate further by watching the video you suggested however it did not work either.

I'm still stuck with the sum of averages not working. I'm guessing that I probably have to extract the averages at a certain level into another table to be able to sum them up from that new table. I didn't had a chance to try yet.

I'll let you know if that works.

 

Thanks for your help.

amitchandak
Super User
Super User

@Toftaf , Assume you have measure M1 which does Avg, And after Level 2 we want SUm

 

Sumx(Values(Table[Level 2]), [M1])

 

For more complex ones you can use isinscope to control what you show at each level

 

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.