Total Sum in each row in a table issue of DAX

nemo189 — Wed, 28 Feb 2024 20:27:08 GMT

Hello Everyone,
I am facing an issue , and unable to solve.
I have this below data. I want 14968 Value for all the voice numbers under "Total Invoice Amount" similar to "Total test Value" column. I have achieved the Total test Value using a calculated column using Earlier fuction, But its not changing dynamically with slicer selection. With slicer selection the value should be 14968. Here Invoice Item Amount Measure2 is a Measure.
The Total Invoice Amount should be "sum of allthe invoice item amount measure 2 grouped by voice number."

I have written the below formulas:

Invoice Item Amount Measure 2 is a Measure,
Invoice Item Amount Measure 2 =
SUMX(
'Invoice Hold Facts',
IF(
'Invoice Hold Facts'[Highest Ranking Indicator] = "Y",
'Invoice Hold Facts'[Invoice Item Amount],
0
)
)
Total test Value is a Calculated column using Invoice Item Amount Measure 2
Total test Value =
CALCULATE(
[Invoice Item Amount Measure 2], FILTER('Invoice Hold Facts','Invoice Hold Facts'[Invoice Number] = EARLIER('Invoice Hold Facts'[Invoice Number]) && 'Invoice Hold Facts'[SUPPLIER_WID] = EARLIER('Invoice Hold Facts'[SUPPLIER_WID]) && 'Invoice Hold Facts'[Highest Ranking Indicator] = "Y"))

------------------------------------------------------------------------------------------------------
I have tried other methods as measures , but nothing is working.
Below are some methods I tried i have commented some methods in it.

Total Amount Measure =
/*CALCULATE(
SUM('Invoice Hold Facts'[Invoice Item Amount]),
FILTER(
'Invoice Hold Facts',
[Invoice Item Amount Measure] = CALCULATE(
SUM('Invoice Hold Facts'[Invoice Item Amount]),
'Invoice Hold Facts'[Highest Ranking Indicator] = "Y"
)
)
)*/
/*SUMX(
SUMMARIZE(
'Invoice Hold Facts',
'Invoice Hold Facts'[Internal Invoice Id],
"TotalInvoiceAmount", [Invoice Item Amount Measure 2]
),
[TotalInvoiceAmount]
)*/
/*SUMX(
VALUES('Invoice Hold Facts'[Invoice Number]),
[Invoice Item Amount Measure 2]
)*/
/*CALCULATE(
SUM('Invoice Hold Facts'[Invoice Item Amount]),
ALLEXCEPT('Invoice Hold Facts', 'Invoice Hold Facts'[Invoice number])
)*/
SUMX(
VALUES('Invoice Hold Facts'[Invoice Number]),
CALCULATE(
SUM('Invoice Hold Facts'[Invoice Item Amount])
)
)

PLEASE HELP. THANKYOU

Re: Total Sum in each row in a table issue of DAX

Anonymous — Thu, 29 Feb 2024 05:51:34 GMT

Hi nemo189 ,

I read your DAX code carefully, to dynamically calculate the “Total Invoice Amount” based on slicer selections, you can create a measure that considers the filtered context.

I think you can create a Measure for Total Invoice Amount.

Total Invoice Amount = CALCULATE( [Invoice Item Amount Measure 2], FILTER( ALLSELECTED('Invoice Hold Facts'), 'Invoice Hold Facts'[Highest Ranking Indicator] = "Y" ) )

The ALLSELECTED function ensures that the slicer selections are considered. The filter condition restricts the calculation to rows where the “Highest Ranking Indicator” is “Y.”

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Total Sum in each row in a table issue of DAX in DAX Commands and Tips

Total Sum in each row in a table issue of DAX

Re: Total Sum in each row in a table issue of DAX