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

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
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] )

 

gulbazk's avatar
gulbazk
Regular Visitor
4 years ago

Thank u soooo much Bro. It worked for me.