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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tony-G_2020
Frequent Visitor

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   

 

 

1 ACCEPTED SOLUTION

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])

vcgaomsft_0-1703124513534.png

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

View solution in original post

3 REPLIES 3
Bibiano_Geraldo
Resolver I
Resolver I

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

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.

 

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])

vcgaomsft_0-1703124513534.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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