Column Total is not returning the desired value

[o59393]

Hi all

I need to have in my dax the total from a column, however the way I have my formula it not working properly.

Basically I want to have the sum of the column called "Ingredient dax above parameter" seen below:

As seen, the value returned is 29,916 but I would like to have the sum of those values which would give me: 6,143

The dax for that column is:

Ingredient dax above parameter = 
IF([Value]>Parameter[Parameter Value]
,
(Parameter[Parameter Value]*SUM(Query1[[Totals]]])*5.678*0.01)/1000
,
BLANK())

For the parameter I am using 7.4 as an example

In the excel attached I explain were the value of 29,915 from that column comes from. As well as the result I would like toi have, which is in cell G63, pretty much is the sum of the individual values.

Please let me know if this is clear, I have some difficulties uploading a pbix due to sensible data.

Excel: https://1drv.ms/x/s!ApgeWwGTKtFdhlo5bN320A3Y4Y7f?e=JJwkjM

Thank you.

[v-gizhi-msft · Accepted solution]

Hi,

Please try this:

Ingredient dax above parameter =
SUMX (
    GROUPBY ( Query1, Query1[Value] ),
    CALCULATE (
        IF (
            MAX ( Query1[Value] ) > Parameter[Parameter Value],
            (
                Parameter[Parameter Value] * SUM ( Query1[Total] ) * 5.678 * 0.01
            ) / 1000,
            BLANK ()
        )
    )
)

The result shows:

Here is the test pbix file:

pbix 

Hope this helps.

If my answer has solved your issue, please mark it as a solution for others to see.
Thanks!

Best Regards,

Giotto

[amitchandak]

Try like

sux(Summarize(Table,table[country],table[Product],"_1",[Ingredient dax above parameter]),[_1])