Forum Discussion

GCH_FR's avatar
GCH_FR
Helper I
2 years ago

SUM BETWEEN DIFFERENTS TABLES

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
 

With Codification Nok : 

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
 
Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

    • GCH_FR's avatar
      GCH_FR
      Helper I
      2 years ago

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

       

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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    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:

     

     

    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

    • GCH_FR's avatar
      GCH_FR
      Helper I
      2 years ago

      Hi Anonymous 

       

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

       

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