Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GCH_FR
Frequent Visitor

SUM BETWEEN DIFFERENTS TABLES

‎08-02-2024 04:46 AM

Hello Everybody

 

Further to the help concerning my first topic (Re: How to do a calculation between date variable - Microsoft Fabric Community)

 

TEST BPA =

SUMMARIZE(
        FILTER(
                 CROSSJOIN(CALENDAR(MIN(FUP[DATE DEBUT ACTION BPA]),MAX('FUP'[DATE FIN ACTION BPA])),'FUP')
                ,[Date]>=[DATE DEBUT ACTION BPA]&&[Date]<=[DATE FIN ACTION BPA])
            ,[Date],FUP[TEMPS THEORIQUE BPA PAR SEMAINE], FUP[CODIFICATION])
 
TEST BPE = 
    SUMMARIZE(
        FILTER(
                 CROSSJOIN(CALENDAR(MIN(FUP[DATE DEBUT ACTION BPE]),MAX('FUP'[DATE FIN ACTION BPE])),'FUP')
                ,[Date]>=[DATE DEBUT ACTION BPE]&&[Date]<=[DATE FIN ACTION BPE])
            ,[Date],FUP[TEMPS THEORIQUE BPE PAR SEMAINE], FUP[CODIFICATION])
 
I'd like to havec a new table with TEMPS THEORIQUE BPE PAR SEMAINE + TEMPS THEORIQUE BPA PAR SEMAINE
 
All my table are link with a Calendar Table. I try with "RELATEDTABLE()" but I've received some weird data
 
TEST = SUMX(RELATEDTABLE(CALENDRIER),'TEST BPA'[TEMPS THEORIQUE CUMULE])+SUMX(RELATEDTABLE(CALENDRIER),'TEST BPE'[TEMPS THEORIQUE CUMULE BPE])
 
Could you help please, i'll be very grateful
 
Edit : I've found this way to do that
 
TEMPS THEORIQUE PRE+BPA+BPE = SUMX(
    RELATEDTABLE(CALENDRIER),
    CALCULATE(SUM('1-CALCUL TEMPS THEORIQUE BPA'[TEMPS THEORIQUE BPA PAR JOUR])+SUM('1-CALCUL TEMPS THEORIQUE BPE'[TEMPS THEORIQUE BPE PAR SEMAINE]))
)
 
It's ok well but i want a other parameter like "CODIFICATION " 
 
Without codification : Ok
 
GCH_FR_0-1722601333890.png

With Codification Nok : 

GCH_FR_1-1722601383152.png

2024-S01 / 1-ORGANISATION / GESTION = 6.24 +.89 not 16.64

 
 
 
Here my table 
 
ODIFICATIONCODIFICATION 2NUMERO DOCUMENTDESIGNATIONDATE PREBUDGET PRELISSAGE PREDATE PRE FORCEEDATE DEBUT ACTION PRENO SEMAINE DEBUT ACTION PRETEMPS THEORIQUE PRE PAR JOURDATE FIN ACTION PREDATE BPABUDGET BPALISSAGE BPADATE BPA FORCEEDATE DEBUT ACTION BPANO SEMAINE DEBUT ACTION BPATEMPS THEORIQUE BPA PAR JOURDATE FIN ACTION BPADATE BPEBUDGET BPELISSAGE BPEDATE BPE FORCEEDATE DEBUT ACTION BPETEMPS THEORIQUE BPE PAR JOURDATE FIN ACTION BPE
1-ORGANISATIONORGANISATIONORGASUIVI30/12/2023102 30/12/2023 0,7142857113/01/202431/10/202443040 25/01/202451,5357142931/10/2024       
2-CONCEPTIONSCHÉMAS DRD LOT840YSC345HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3450      26/01/202466,553 05/01/202423,1690476226/01/202415/03/2024153 23/02/20240,7142857115/03/2024
2-CONCEPTIONSCHÉMAS DRD LOT840YSC453HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4530      15/02/2024403 25/01/202451,904761915/02/202429/03/2024153 08/03/20240,7142857129/03/2024
2-CONCEPTIONSCHÉMAS DRD LOT840YSC463HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4630      15/02/2024403 25/01/202451,904761915/02/202429/03/2024153 08/03/20240,7142857129/03/2024
2-CONCEPTIONSCHÉMAS DRD LOT840YSC462HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4620      06/02/2024403 16/01/202441,904761906/02/202421/03/2024153 29/02/20240,7142857121/03/2024
2-CONCEPTIONSCHÉMAS DRD LOT840YSC335HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3350      06/02/2024403 16/01/202441,904761906/02/202420/03/2024153 28/02/20240,7142857120/03/2024
2-CONCEPTIONSCHÉMAS DRD LOT840YSC344HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3440      06/02/2024403 16/01/202441,904761906/02/202420/03/2024153 28/02/20240,7142857120/03/2024
2-CONCEPTIONSCHÉMAS DRD LOT840YSC606HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 6060      09/02/2024403 19/01/202441,904761909/02/202429/03/2024153 08/03/20240,7142857129/03/2024
2-CONCEPTIONNOMENCLATURE840YBM03HVAC - EQUIPMENTS IN ACCOMODATIONS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
2-CONCEPTIONNOMENCLATURE840YBM01HVAC - MAIN EQUIPMENTS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
2-CONCEPTIONNOMENCLATURE840YBM02HVAC - IL/OL 0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
2-CONCEPTIONNOMENCLATURE840YBM04HVAC - EQUIPMENTS IN ACR0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
2-CONCEPTIONNOMENCLATURE840YBM05HVAC - SEATINGS 0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
2-CONCEPTIONNOMENCLATURE840YBM06HVAC - MISCELLANEOUS ELECTRICAL EQUIPMENTS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
2-CONCEPTIONSCHÉMAS PID TU862YACS462HHVAC - PID TUYAUTAGE (VFC) EN ACR  462H0      06/02/20242,51 30/01/202460,3571428606/02/202421/03/20242,51 14/03/20240,3571428621/03/2024
2-CONCEPTIONSCHÉMAS PID TU863YACS462HHVAC - PID TUYAUTAGE (VFE) EN ACR  462H0      06/02/20242,51 30/01/202460,3571428606/02/202421/03/20242,51 14/03/20240,3571428621/03/2024
 
Labels:
Message 1 of 5
291 Views
0
4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

‎08-07-2024 12:16 AM

lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
Hi  @GCH_FR ,

 

This should occur because [ANNEE_SEM]=2024-S01&&[CODIFICATION]="1-ORGANISATION" or ="2- CONCEPTION" are all on the the same date, which is treated as a group when calculating.

You can set an index for each row, and each row has a separate label for the separate grouping.

 

Here are the steps you can follow：

1. Add Column – Index Column – From 1.

vyangliumsft_0-1723014613540.png

2. Create measure.

 

Measure =
SUMX(
    FILTER(ALL('1-CALCUL TEMPS THEORIQUE BPA'),
    '1-CALCUL TEMPS THEORIQUE BPA'[Index]<=MAX('1-CALCUL TEMPS THEORIQUE BPA'[Index])),[TEMPS THEORIQUE BPA PAR JOUR])

 

3. Result:

vyangliumsft_1-1723014613544.png

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

SUM BETWEEN DIFFERENTS TABLES.pbix
Message 4 of 5
126 Views
0
GCH_FR
Frequent Visitor
In response to v-yangliu-msft

‎08-08-2024 01:19 AM

Hi @v-yangliu-msft 

 

Thank you for your reply. It wasn't quite what I expected, but perhaps I wasn't very clear about what I wanted.

I found a solution by looking in different forums, the only problem is that my dates have to be sorted in descending order 

 

CALCULATE (
    SUM ( '1-CALCUL TEMPS THEORIQUE BPA'[TEMPS THEORIQUE BPA PAR SEMAINE] ),
    FILTER (
        ALL ( '1-CALCUL TEMPS THEORIQUE BPA' ),
        '1-CALCUL TEMPS THEORIQUE BPA'[CODIFICATION] = EARLIER ( '1-CALCUL TEMPS THEORIQUE BPA'[CODIFICATION] )
            && '1-CALCUL TEMPS THEORIQUE BPA'[Date] <= EARLIER ( '1-CALCUL TEMPS THEORIQUE BPA'[Date] )
    )
)
 
GCH_FR_0-1723103319295.png

 

I've found a way to have my cumulative BPA / BPE / PRE by make a UNION between them

 

UNION(
    SUMMARIZE('1-CALCUL TEMPS THEORIQUE BPA','1-CALCUL TEMPS THEORIQUE BPA'[Date],'1-CALCUL TEMPS THEORIQUE BPA'[CODIFICATION],'1-CALCUL TEMPS THEORIQUE BPA'[TEMPS THEORIQUE BPA PAR SEMAINE]),
    SUMMARIZE('1-CALCUL TEMPS THEORIQUE BPE','1-CALCUL TEMPS THEORIQUE BPE'[Date],'1-CALCUL TEMPS THEORIQUE BPE'[CODIFICATION],'1-CALCUL TEMPS THEORIQUE BPE'[TEMPS THEORIQUE BPE PAR SEMAINE])
    )
Message 5 of 5
109 Views
0
lbendlin
Super User
Super User

‎08-03-2024 06:46 AM

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

Message 2 of 5
206 Views
0
GCH_FR
Frequent Visitor
In response to lbendlin

‎08-05-2024 12:24 AM

Hi @Ibendlin

 

I would like to achieve the time per day/week and the cumulative time per day/week per "Codification" with my different milestone BPA - BPE - PRE

Like 

 

2024-S01 / 1-ORGANISATION / GESTION / SUIVI ETUDES /

TIME BPA-BPE PER DAY 0.89+29.93

CUMULATIVE TIME = 2023-S52 + 2024-S01

 

And for the moment i don't arrive to acheive to integrate the "Codification" in my cumulative time 

To have per week per codifiation the cumulative time by week

 

TEMPS THEORIQUE CUMULE BPA =
SUMX(
    FILTER(
        ALLSELECTED('1-CALCUL TEMPS THEORIQUE BPA'),
        '1-CALCUL TEMPS THEORIQUE BPA'[Date]<=MAXX('1-CALCUL TEMPS THEORIQUE BPA',
        '1-CALCUL TEMPS THEORIQUE BPA'[Date])),
    '1-CALCUL TEMPS THEORIQUE BPA'[TEMPS THEORIQUE BPA PAR JOUR])

GCH_FR_0-1722856956910.png

 

And in the cumulative time per week per codification with all the milestone

TEMPS THEORIQUE PRE+BPA+BPE = SUMX(
    RELATEDTABLE(CALENDRIER),
    CALCULATE(SUM('1-CALCUL TEMPS THEORIQUE BPA'[TEMPS THEORIQUE BPA PAR JOUR])+SUM('1-CALCUL TEMPS THEORIQUE BPE'[TEMPS THEORIQUE BPE PAR SEMAINE]))
)

 

GCH_FR_0-1722841960565.png

 

Message 3 of 5
161 Views
0

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 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.

Sept NL Carousel

Fabric Community Update - September 2024

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

View All
Top Solution Authors
View All
Top Kudoed Authors
View All