conditional measure total is wrong in power bi table matrix

[Gulbaz]

Hi
I have Four Tables, Calendar, Location, Sales and Targets. They have Relationships with like one to many as below
Calendar Date with Sales Date
Calendar Date with Targets Date
Location ID with Sales Location ID
Location ID with Targets Location ID

I am Trying to achieve a result through Measure with Conditions that if the Calendar Month is < Current Month then It should Some Sales Table Amount Column, Otherwise Targets Table Amount Column. Now when I put both Measures on Table Matrix with Year and Month on Rows and Conditional Measure on Column, Month By Month it gives the right Calculation but on total level it is giving incorrect Amount.

I am Using the Following Simple If Logic 

IF(MONTH(MAX('Calender 2'[Date])<MONTH(TODAY())),

SUM('Value Entry Stock All Items'[Sales Amount (Actual)]),

SUM(Targets[JAN Target]))

As per the above image the value on each month level is correct but the total is wrong, instead of summing up the visual values in the column it is applying the conditional logic to the totals level which is wrong. It should sum up to 143,809,001.

We have tried summing it up through summarize and sumx but due to limited knowlede we are not able to resolve it. Please help!

Thanks in Advance

[DataInsights · Accepted solution]

@Gulbaz,

This can be handled with a separate totals measure. I'm assuming your date table has a [Year Month] column (or similar). If not, it's worth adding to your date table.

Base Measure =
IF (
    MONTH ( MAX ( 'Calender 2'[Date] ) < MONTH ( TODAY () ) ),
    SUM ( 'Value Entry Stock All Items'[Sales Amount (Actual)] ),
    SUM ( Targets[JAN Target] )
)

Total Measure =
SUMX ( VALUES ( 'Calender 2'[Year Month] ), [Base Measure] )

[Anonymous]

Thanks @DataInsights !! It worked for me.. Could you please explain whats happeneing in back end..

[KelvinTH4444]

Thank you for this solution that I saw the same too. I wanna know why you use [Year Month]

[DataInsights]

@KelvinTH4444,

The field [Year Month] provides uniqueness (based on the provided screenshot) in a single field. Otherwise, you would need to summarize unique combinations of Year and Month.

[KelvinTH4444]

I see. Thank you for your descriprtion. 😊

[Martin74]

Hello @DataInsights ,

Athough your answer is over a year old, it helped me really a lot. I had a measure that was not calculating the total in a good way. After seraching in the community I tried your answer and with some modification it worked just fine. Now the totals are right, so if the customer need the min/max etc. it also worked.

Keep up answering, thanks a lot.

With kind regards,

Martin

 

[DataInsights]

@Martin74,

Thanks for the feedback! Glad to hear this solution helped. See the solution below for a different approach that uses KEEPFILTERS.

https://community.fabric.microsoft.com/t5/Desktop/Cumulative-Value-Not-Totalling-Correctly/m-p/27624... 

On a side note, consider using DIVIDE instead of the division operator to handle division by zero. Good luck!

[Gulbaz]

Thaank u sooo much bro! It worked for me.

[gulbazk]

Thank u soooo much Bro. It worked for me.