How to Apply Monthly Target to put in Line Chat to See by Day based on Calculation

[Chanleakna123]

hi all , 

i have 2 tables , 1 is Target Table , 1 is Actual NSR Table and another 1 is Date Table.

All Table are in Relationship together. 

I use the Formula to get DRR Actual NSR  by : Target Table / Working day ( based on Month ) . 

My Problem is : i want DRR Actual NSR to be apply to All Day during the Month

The problem isI have only Monthly Target , Not Daily Target , But i needs to track sales by Day , with DRR ( Daily Run Rate ) . 

My Purpose is to see the same numer of 370,792 in All Day based on Month i select , 

everything is based on month selected , DRR will be differeent by Month . 

[AntrikshSharma · Accepted solution]

"i want DRR Actual NSR to be apply to All Day during the Month" Does this help? if not, then please upload the file.

CALCULATE([DRR Actual NSR], ALL( Dates[Date] ) )

[Chanleakna123]

@AntrikshSharma it's work , thanks you so much , cheers

[amitchandak]

@Chanleakna123 , Try a measure like

sumx(summarize(table, Date[Month Year],"_1", calculate(max(Table[DRR Actual NSR]),filter(all(Date),Date[Month Year] =max(Date[Month Year])))),[_1])

[Chanleakna123]

hi @amitchandak  , i am sorry i didn't get it . what is _1 here ? 
need further explanation. 

sorry 

[AntrikshSharma]

It is a virtual column of the virtual table created inside SUMMARIZE so that it can be used later in other functions calling it. Another version of this measure is below which is a good practice as SUMMARIZE should only be used for Grouping.

Measure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( table, Date[Month Year] ),
        "MyNewColumn", CALCULATE (
            MAX ( Table[DRR Actual NSR] ),
            FILTER ( ALL ( Date ), Date[Month Year] = MAX ( Date[Month Year] ) )
        )
    ),
    [MyNewColumn]
)

[Chanleakna123]

@AntrikshSharma @amitchandak 

Both aren't work 😞

[AntrikshSharma]

This is because inside MAX you have a measure, use a column on which you want to use MAX or just remove the MAX and use the measure.

[amitchandak]

@Chanleakna123 , no need of max on DRR actual NSR as it is already a measure

[Chanleakna123]

@amitchandak @AntrikshSharma , i have tested both measure , but aren't work and both shown the same result of 5475 instead of 16235 is the right number

[amitchandak]

@Chanleakna123 , I am creating a table using summarize in formula , in that see there is a column _1 , when I refer that i need to use [_1]