cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
amarantevitor Frequent Visitor
Frequent Visitor

Wrong Grand Totals Using SWITCH

Inscrição RAP =
VAR InscricaoRAP = CALCULATE(
SUMX(
fEmpenho;
fEmpenho[Saldo - R$ (Conta Contábil)]);
FILTER(fEmpenho;fEmpenho[Conta Contábil] = "622920105"
))
VAR InscricaoRAPLiquidar = CALCULATE(
SUMX(
fEmpenho;
fEmpenho[Saldo - R$ (Conta Contábil)]);
FILTER(fEmpenho;fEmpenho[Conta Contábil] = "531110100"
))

VAR ReinscricaoRAP = CALCULATE(
SUMX(
fEmpenho;
fEmpenho[Saldo - R$ (Conta Contábil)]);
FILTER(fEmpenho;fEmpenho[Conta Contábil] = "531210000"
))

VAR InscricaoConsolidada = SWITCH(
TRUE();
InscricaoRAP <> BLANK(); InscricaoRAP;
InscricaoRAPLiquidar <> BLANK(); InscricaoRAPLiquidar;
ReinscricaoRAP
)
RETURN

InscricaoConsolidada
 
Total Errado.JPG
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Wrong Grand Totals Using SWITCH

Hi @amarantevitor ,

 

We can try to use the following measure to meet your requirement, the 'Table'[Column] means the first column you used in the table visual:

 

Inscrição RAP =
SUMX (
    SUMMARIZE (
        'Table';
        'Table'[Column];
        "Result";
        VAR InscricaoRAP =
            CALCULATE (
                SUM ( fEmpenho[Saldo - R$ (Conta Contábil)] );
                fEmpenho[Conta Contábil] = "622920105"
            )
        VAR InscricaoRAPLiquidar =
            CALCULATE (
                SUM ( fEmpenho[Saldo - R$ (Conta Contábil)] );
                fEmpenho[Conta Contábil] = "531110100"
            )
        VAR ReinscricaoRAP =
            CALCULATE (
                SUM ( fEmpenho[Saldo - R$ (Conta Contábil)] );
                fEmpenho[Conta Contábil] = "531210000"
            )
        VAR InscricaoConsolidada =
            SWITCH (
                TRUE ();
                InscricaoRAP <> BLANK (); InscricaoRAP;
                InscricaoRAPLiquidar <> BLANK (); InscricaoRAPLiquidar;
                ReinscricaoRAP
            )
        RETURN
            InscricaoConsolidada
    );
    [Result]
)

 


If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User II
Super User II

Re: Wrong Grand Totals Using SWITCH

Could you tell us what you expect the totals to be ? and which lines are in error ?





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




amarantevitor Frequent Visitor
Frequent Visitor

Re: Wrong Grand Totals Using SWITCH

The lines are corrects, just the grand total are wrong.

Super User II
Super User II

Re: Wrong Grand Totals Using SWITCH

Generally, that kind of problem means there is some issue with the filter context.

if you RETURN each of the VARs do you see the results you expect

for example

Inscrição RAP =
VAR InscricaoRAP = CALCULATE(
SUMX(
fEmpenho;
fEmpenho[Saldo - R$ (Conta Contábil)]);
FILTER(fEmpenho;fEmpenho[Conta Contábil] = "622920105"
))
VAR InscricaoRAPLiquidar = CALCULATE(
SUMX(
fEmpenho;
fEmpenho[Saldo - R$ (Conta Contábil)]);
FILTER(fEmpenho;fEmpenho[Conta Contábil] = "531110100"
))

 

VAR ReinscricaoRAP = CALCULATE(
SUMX(
fEmpenho;
fEmpenho[Saldo - R$ (Conta Contábil)]);
FILTER(fEmpenho;fEmpenho[Conta Contábil] = "531210000"
))

 

VAR InscricaoConsolidada = SWITCH(
TRUE();
InscricaoRAP < 0; InscricaoRAP;
InscricaoRAPLiquidar <> BLANK(); InscricaoRAPLiquidar;
ReinscricaoRAP
)
RETURN

 

ReinscricaoRAP




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Super User I
Super User I

Re: Wrong Grand Totals Using SWITCH

I'm unsure exactly how your code is working without more inofmration, but the grand total is not a sum of all the rows.

 

Instead, it is the DAX calculation with no row level context - the value in "rows" of your matrix.

 

I suiggest reading this article:

https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

 

Appreciate your Kudos
Connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Community Support Team
Community Support Team

Re: Wrong Grand Totals Using SWITCH

Hi @amarantevitor ,

 

We can try to use the following measure to meet your requirement, the 'Table'[Column] means the first column you used in the table visual:

 

Inscrição RAP =
SUMX (
    SUMMARIZE (
        'Table';
        'Table'[Column];
        "Result";
        VAR InscricaoRAP =
            CALCULATE (
                SUM ( fEmpenho[Saldo - R$ (Conta Contábil)] );
                fEmpenho[Conta Contábil] = "622920105"
            )
        VAR InscricaoRAPLiquidar =
            CALCULATE (
                SUM ( fEmpenho[Saldo - R$ (Conta Contábil)] );
                fEmpenho[Conta Contábil] = "531110100"
            )
        VAR ReinscricaoRAP =
            CALCULATE (
                SUM ( fEmpenho[Saldo - R$ (Conta Contábil)] );
                fEmpenho[Conta Contábil] = "531210000"
            )
        VAR InscricaoConsolidada =
            SWITCH (
                TRUE ();
                InscricaoRAP <> BLANK (); InscricaoRAP;
                InscricaoRAPLiquidar <> BLANK (); InscricaoRAPLiquidar;
                ReinscricaoRAP
            )
        RETURN
            InscricaoConsolidada
    );
    [Result]
)

 


If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support Team
Community Support Team

Re: Wrong Grand Totals Using SWITCH

Hi @amarantevitor ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors