Forum Discussion
Problem with Grand Total and SubTotal
Hello, I have a big problem with a measure and I don't know how to solve it.
*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.
- 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.
- the relationship between these two tables is by the PK (ID Student + year enrollment)
- At the moment to analyze the matrix the amount is correct for each student but not for the groups -> BACHELOR DEGREE ->UNIVERSITY -> GRAND TOTAL.
- 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.
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
-
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.
Try
Facturación _Prevision_21 = SUMX ( VALUES ( Table[Línea de negocio] ), [Facturación_Previsión21] )
1 Reply
- PaulDBrownCommunity Champion
Try
Facturación _Prevision_21 = SUMX ( VALUES ( Table[Línea de negocio] ), [Facturación_Previsión21] )