Forum Discussion

Gulbaz's avatar
Gulbaz
Frequent Visitor
4 years ago
Solved

conditional measure total is wrong in power bi table matrix

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...
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    4 years ago

    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] )

     

KelvinTH4444's avatar
KelvinTH4444
Regular Visitor
2 years ago

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

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
2 years ago

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's avatar
    KelvinTH4444
    Regular Visitor
    2 years ago

    I see. Thank you for your descriprtion. ðŸ˜Š