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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Problem with Grand Total and SubTotal

Hello, I have a big problem with a measure and I don't know how to solve it. 

 

Dulce_Gamez4_0-1637928551764.png

*the red one is the "Faturación" with the discounts and it works for each student but not with the sum of the group of the bachelor's degree and the yellow one is not considering the personal discounts.

 

  1. In this case, I have 2 data models. The first one with the ID Student (and other things) and the second one with the prices for the reservation fee, the enrollment, tuition, and the discounts of each student. 
  2. the relationship between these two tables is by the PK (ID Student + year enrollment)
  3. At the moment to analyze the matrix the amount is correct for each student but not for the groups -> BACHELOR DEGREE ->UNIVERSITY  -> GRAND TOTAL.
  4. I want to calculate the "PREVISIÓN"  that has a lot of conditions because of the discount of each student (a lot of kinds of discounts concepts). Here is the measure.
  5. Dulce_Gamez4_0-1638178900399.png

    Facturacion_Prevision21 =
    var C70= IF(ISBLANK(MAX(MONTOS_DESC[C70_IMPORTE])),0,MAX(MONTOS_DESC[C70_IMPORTE]))
    var C71= IF(ISBLANK(MAX(MONTOS_DESC[C71_IMPORTE])),0,MAX(MONTOS_DESC[C71_IMPORTE]))
    var C72= IF(ISBLANK(MAX(MONTOS_DESC[C72_IMPORTE])),0,MAX(MONTOS_DESC[C72_IMPORTE]))
    var C73= IF(ISBLANK(MAX(MONTOS_DESC[C73_IMPORTE])),0,MAX(MONTOS_DESC[C73_IMPORTE]))
    var C74= IF(ISBLANK(MAX(MONTOS_DESC[C74_IMPORTE])),0,MAX(MONTOS_DESC[C74_IMPORTE]))
    var dto_reserva_cons = IF(ISBLANK(MAX(MONTOS_DESC[DTO_RESERVA])),0,MAX(MONTOS_DESC[DTO_RESERVA]))
    var dto_matricula_cons = IF(ISBLANK(MAX(MONTOS_DESC[DTO_MATRICULA])),0,MAX(MONTOS_DESC[DTO_MATRICULA]))
    var c90= MAX(MONTOS_DESC[C90_IMPORTE])
    var c91= MAX(MONTOS_DESC[C91_IMPORTE])
    var c92= MAX(MONTOS_DESC[C92_IMPORTE])
    var c93= MAX(MONTOS_DESC[C93_IMPORTE])


    var PVPReserva = CALCULATE(SUM(MONTOS_DESC[PVP_Reserva]))
    var FechaReserva= MAX(MONTOS_DESC[F_RESERVA])
    var Dto_Reserva= IF(C70=0,IF(C72=0,IF(dto_reserva_cons=0,0,dto_reserva_cons),PVPReserva*C72/100),PVPReserva*C70/100)
    var PVPMatricula = CALCULATE(SUM(MONTOS_DESC[PVP_Matricula]))
    var FechaMatricula= MAX(MONTOS_DESC[F_MATRICULA])
    var MATRICULA = IF(PVPMatricula=0,0,IF(ISBLANK(FechaMatricula),0,PVPMatricula-dto_matricula_cons))
    var Dto_Matricula = IF(C70=0,IF(C73=0,IF(dto_matricula_cons=0,0,dto_matricula_cons),PVPMatricula*C73/100),PVPMatricula*C70/100)
    var PVPDocencia = CALCULATE(SUM(MONTOS_DESC[PVP_ENS]))
    var Porcentaje_Descuento = CALCULATE(SUM(MONTOS_DESC[PORCENTAJE_DTO])/100)

    var DTO_ENSENANZA = IF(C70=0,IF(C71=0,IF(C74=0,IF(AND(OR([Sector]=1,[Sector]=2),c91<>0),PVPDocencia*Porcentaje_Descuento,IF(AND((AND(OR([Sector]=1,[Sector]=2),c91=0)),c92<>0),(PVPDocencia+PVPReserva)*Porcentaje_Descuento,IF(AND(OR([Sector]=1,[Sector]=2),Porcentaje_Descuento=0),c90,IF(AND([Sector]=3,c91<>0),PVPDocencia*Porcentaje_Descuento,IF(AND(AND([Sector]=3,c91=0),c92<>0),PVPDocencia*Porcentaje_Descuento,IF(AND([Sector]=3,Porcentaje_Descuento=0),c90,0)))))),PVPDocencia*C74/100),PVPDocencia*C71/100),PVPDocencia*C70/100)

     

    var PagoReserva = IF(ISBLANK(FechaReserva),0,PVPReserva-Dto_Reserva)
    var PendienteReserva= PVPReserva-(PagoReserva+Dto_Reserva)
    var PagoMatricula = IF(MATRICULA>PVPMatricula,PVPMatricula,MATRICULA)
    var PendienteMatricula = PVPMatricula-(PagoMatricula+Dto_Matricula)
    var PendienteDocencia= PVPDocencia- DTO_ENSENANZA

     

    return PagoReserva+PendienteMatricula+PagoMatricula+PendienteMatricula+PendienteDocencia

 

  • SECTOR IS THE UNIVERSITY
  •  Dulce_Gamez4_1-1638180015208.png

    Sector =
    var a = SELECTEDVALUE(MONTOS_DESC[VC_SECTOR])

    return
    SWITCH( TRUE(),
    a = "OPEN" , 1,
    a = "CF" , 2,
    a = "UAX" , 3,
    0)

I have spent a lot of time trying to solve it... could be something with hierarchy or to utilize SUMX or MAXX but I have no deeper knowledge and skills to work with that to see if that is the problem or not...   If you could help me I will appreciate it. 

thank you. 

 

 

 

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Try 

Facturación _Prevision_21 =
SUMX ( VALUES ( Table[Línea de negocio] ), [Facturación_Previsión21] )




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

1 REPLY 1
PaulDBrown
Community Champion
Community Champion

Try 

Facturación _Prevision_21 =
SUMX ( VALUES ( Table[Línea de negocio] ), [Facturación_Previsión21] )




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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