cancel
Showing results 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

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

 Code period Total VOL Industry Total VOL Expecting Result A JUL22 - JUN23 842862353 50,824,979,816 26,967,912,224 B JUL22 - JUN23 2188041837 50,824,979,816 26,967,912,224 C JUL22 - JUN23 413636168 50,824,979,816 26,967,912,224 D JUL22 - JUN23 1301719538 50,824,979,816 26,967,912,224 E JUL22 - JUN23 566995382 50,824,979,816 26,967,912,224 F JUL22 - JUN23 184348921 50,824,979,816 26,967,912,224 G JUL22 - JUN23 7554358883 50,824,979,816 26,967,912,224 H JUL22 - JUN23 1069801205 50,824,979,816 26,967,912,224 I JUL22 - JUN23 1021097521 50,824,979,816 26,967,912,224 J JUL22 - JUN23 9794240 50,824,979,816 26,967,912,224 K JUL22 - JUN23 168225577 50,824,979,816 26,967,912,224 L JUL22 - JUN23 73997960 50,824,979,816 26,967,912,224 M JUL22 - JUN23 682858701 50,824,979,816 26,967,912,224 N JUL22 - JUN23 104595188 50,824,979,816 26,967,912,224 O JUL22 - JUN23 449559508 50,824,979,816 26,967,912,224 P JUL22 - JUN23 982751506 50,824,979,816 26,967,912,224 Q JUL22 - JUN23 3833822348 50,824,979,816 26,967,912,224 R JUL22 - JUN23 131052476 50,824,979,816 26,967,912,224 S JUL22 - JUN23 71448658 50,824,979,816 26,967,912,224 T JUL22 - JUN23 4037626142 50,824,979,816 26,967,912,224 U JUL22 - JUN23 1178670751 50,824,979,816 26,967,912,224 V JUL22 - JUN23 100647361 50,824,979,816 26,967,912,224 26,967,912,224

1 ACCEPTED SOLUTION
Community Support

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 3
Resolver I

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

Frequent Visitor

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.

Community Support

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