Forum Discussion

sandragocj27's avatar
sandragocj27
Frequent Visitor
8 years ago
Solved

problem total sum with a measure

Hi everybody, 

I've got two related tables, and I'm trying to make this measure:


TotalSales = switch(max(Service[ServiceType]);

"A1";[ActualSales];
"A2";[ActualSales];
"A3";[ActualSales]*(1+'Parámeter3'[Parameter A3]/100);
"A5";[ActualSales];
"A5";[ActualSales];
"A6";[ActualSales]*(1+'Parámeter4'[Parameter A4]/100);
[ActualSales])

 

 

 

It works perfectly, but in the total the sum is not correct, any idea?

Thanks a lot!!

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    sandragocj27

     

    Try with following

    What is your firstColumn i.e the Column used to Group Values

     

    TotalSales =
IF (
    HASONEFILTER ( TableName[ColumnusedasRowField] ),
    SWITCH (
        MAX ( Service[ServiceType] ),
        "A1", [ActualSales],
        "A2", [ActualSales],
        "A3", [ActualSales]
            * ( 1
            + 'Parámeter3'[Parameter A3] / 100 ),
        "A5", [ActualSales],
        "A5", [ActualSales],
        "A6", [ActualSales]
            * ( 1
            + 'Parámeter4'[Parameter A4] / 100 ),
        [ActualSales]
    ),
    SUMX (
        VALUES ( TableName[ColumnusedasRowField] ),
        CALCULATE (
            SWITCH (
                MAX ( Service[ServiceType] ),
                "A1", [ActualSales],
                "A2", [ActualSales],
                "A3", [ActualSales]
                    * ( 1
                    + 'Parámeter3'[Parameter A3] / 100 ),
                "A5", [ActualSales],
                "A5", [ActualSales],
                "A6", [ActualSales]
                    * ( 1
                    + 'Parámeter4'[Parameter A4] / 100 )
            )
        )
    )
)

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    sandragocj27

     

    Try with following

    What is your firstColumn i.e the Column used to Group Values

     

    TotalSales =
IF (
    HASONEFILTER ( TableName[ColumnusedasRowField] ),
    SWITCH (
        MAX ( Service[ServiceType] ),
        "A1", [ActualSales],
        "A2", [ActualSales],
        "A3", [ActualSales]
            * ( 1
            + 'Parámeter3'[Parameter A3] / 100 ),
        "A5", [ActualSales],
        "A5", [ActualSales],
        "A6", [ActualSales]
            * ( 1
            + 'Parámeter4'[Parameter A4] / 100 ),
        [ActualSales]
    ),
    SUMX (
        VALUES ( TableName[ColumnusedasRowField] ),
        CALCULATE (
            SWITCH (
                MAX ( Service[ServiceType] ),
                "A1", [ActualSales],
                "A2", [ActualSales],
                "A3", [ActualSales]
                    * ( 1
                    + 'Parámeter3'[Parameter A3] / 100 ),
                "A5", [ActualSales],
                "A5", [ActualSales],
                "A6", [ActualSales]
                    * ( 1
                    + 'Parámeter4'[Parameter A4] / 100 )
            )
        )
    )
)