Wrong total when using DISTINCT and SUMX

[mvallee]

Hi,

I am quite new to DAX and I am facing my first major issue.

I have a big table in my Power BI dashboard where each column is a calculated measure that uses a selected "year N" value given from a slicer. So each measure formula contains this filter : FILTER(Projeco, Projeco[Année] = SELECTEDVALUE(AllYears[Année]).

The measure that gives me trouble concerns the "Decalage" value for "year N". For one row, I need to make a DISTINCT because the Decalage is given for a total year, but each row of my initial database corresponds to a dayly entry.

Here is my calculated measure : 

Décalage en N =

CALCULATE(

SUMX(DISTINCT(Projeco[Décalage]), Projeco[Décalage]), FILTER(Projeco, Projeco[Année] = SELECTEDVALUE(AllYears[Année]))

)

For each row the result is just fine, but the grand total is wrong.

Why ? Because DISTINCT is called on all values before the sum is done, so if a calculated value appears more than once in the table, it is not counted.

How can I solve that ? Thanks for your help.

[mvallee]

Hi,

I don't think I need to change the FILTER linked to the "year" slicer, I think the trouble resides in the SUMX used after the DISTINCT.

I also tried using VALUES instead of DISTINCT, with no success.

[v-jingzhang]

Hi @mvallee 

You may try a measure like 

Measure Total = IF(HASONEVALUE(Projeco[Décalage]), [Measure1], SUMX(VALUES(Projeco[Décalage]), [Measure1]))

If you are using a slicer to select a year value, you don't need to add FILTER(Projeco, Projeco[Année] = SELECTEDVALUE(AllYears[Année]) to the measures. It is because the filter will be applied to the measures automatically when you select a value in the slicer.

If this doesn't solve your problem, can you share some dummy data and expected result which can help us understand your requirement better? We'd like to know what is the wrong result currently and what should be the correct result more clearly. Thank you.

Regards,

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[amitchandak]

@mvallee Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

can you try like

Décalage en N =
CALCULATE(
SUMX(values(Projeco[Décalage]), Projeco[Décalage]), FILTER(Projeco, Projeco[Année] in allselected(AllYears[Année]))
)