Forum Discussion

tony-G_2020's avatar
tony-G_2020
Frequent Visitor
2 years ago
Solved

Issue when tally Total in a column using DAX

Hi,

 

Why my Industry Total VOL is different to my expecting Result column?  Thank you

 

Here is my DAX code 

 

Table =

var _Result =
CALCULATETABLE(
SUMMARIZE (
FILTER (
FI_Details,
FI_Details[Framework]="A" &&
FI_Details[Membership_Capacity]="IA" &&
FI_Details[Status]="Active" &&
FI_Details[startMergedDate] = "-" &&
FI_Details[startMergingDate] = "-" &&
FI_Details[startInactiveDate] = "-" &&
FI_Details[period]= "JUL22 - JUN23"
),
FI_Details[Code],
FI_Details[period],

"Total VOL", CALCULATE( [Total VOL] ),
"Indusrtry Total VOL", CALCULATE( [Total VOL], all(FI_Details) )

)
)

RETURN
_Result

 

CodeperiodTotal VOLIndustry Total VOL Expecting Result
AJUL22 - JUN23842862353                                         50,824,979,816         26,967,912,224
BJUL22 - JUN232188041837                                         50,824,979,816         26,967,912,224
CJUL22 - JUN23413636168                                         50,824,979,816         26,967,912,224
DJUL22 - JUN231301719538                                         50,824,979,816         26,967,912,224
EJUL22 - JUN23566995382                                         50,824,979,816         26,967,912,224
FJUL22 - JUN23184348921                                         50,824,979,816         26,967,912,224
GJUL22 - JUN237554358883                                         50,824,979,816         26,967,912,224
HJUL22 - JUN231069801205                                         50,824,979,816         26,967,912,224
IJUL22 - JUN231021097521                                         50,824,979,816         26,967,912,224
JJUL22 - JUN239794240                                         50,824,979,816         26,967,912,224
KJUL22 - JUN23168225577                                         50,824,979,816         26,967,912,224
LJUL22 - JUN2373997960                                         50,824,979,816         26,967,912,224
MJUL22 - JUN23682858701                                         50,824,979,816         26,967,912,224
NJUL22 - JUN23104595188                                         50,824,979,816         26,967,912,224
OJUL22 - JUN23449559508                                         50,824,979,816         26,967,912,224
PJUL22 - JUN23982751506                                         50,824,979,816         26,967,912,224
QJUL22 - JUN233833822348                                         50,824,979,816         26,967,912,224
RJUL22 - JUN23131052476                                         50,824,979,816         26,967,912,224
SJUL22 - JUN2371448658                                         50,824,979,816         26,967,912,224
TJUL22 - JUN234037626142                                         50,824,979,816         26,967,912,224
UJUL22 - JUN231178670751                                         50,824,979,816         26,967,912,224
VJUL22 - JUN23100647361                                         50,824,979,816         26,967,912,224
                                             26,967,912,224   

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi tony-G_2020 ,


    Try breaking it into two steps. That should work.
    New table:

    Table =
VAR _Result =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                FI_Details,
                FI_Details[Framework] = "A"
                    && FI_Details[Membership_Capacity] = "IA"
                    && FI_Details[Status] = "Active"
                    && FI_Details[startMergedDate] = "-"
                    && FI_Details[startMergingDate] = "-"
                    && FI_Details[startInactiveDate] = "-"
                    && FI_Details[period] = "JUL22 - JUN23"
            ),
            FI_Details[Code],
            FI_Details[period],
            "Total VOL", CALCULATE ( [Total VOL] )
        )
    )
RETURN
    _Result

     New calculated column:

    Indusrtry Total VOL = SUM('Table'[Total VOL])



    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

3 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    2 years ago

    Because in the statement bellow, you are clearing all filter using ALL function.
    "Indusrtry Total VOL", CALCULATE( [Total VOL], all(FI_Details) )"

     

    If this help you, mark this answer as solution

    • tony-G_2020's avatar
      tony-G_2020
      Frequent Visitor
      2 years ago

      I removed All functions but I still not getting 26,967,912,224. Instead, I got the identical figure as in "Total VOL" column. I want the "Total VOL" aggregation in the "Industry Total VOL" column.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi tony-G_2020 ,


        Try breaking it into two steps. That should work.
        New table:

        Table =
VAR _Result =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                FI_Details,
                FI_Details[Framework] = "A"
                    && FI_Details[Membership_Capacity] = "IA"
                    && FI_Details[Status] = "Active"
                    && FI_Details[startMergedDate] = "-"
                    && FI_Details[startMergingDate] = "-"
                    && FI_Details[startInactiveDate] = "-"
                    && FI_Details[period] = "JUL22 - JUN23"
            ),
            FI_Details[Code],
            FI_Details[period],
            "Total VOL", CALCULATE ( [Total VOL] )
        )
    )
RETURN
    _Result

         New calculated column:

        Indusrtry Total VOL = SUM('Table'[Total VOL])



        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group