Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate Measure unless other column is X value

Hi guys,

 

I currently have the below Measure. This works perfectly for all except for Location Code of 'SP'.

 

Does anyone know how I would adjust the measure so that if Location Code = SP then value = 450000. The intention is to use this in a Matrix visualisation, so Total would need to include this static value.

 

Currently Projected Sales =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
'Current Month Sales',
'Location'[Country Code],
'Location'[Location Type],
'Location'[Location Code]
)
,
"Projected Sales", ((([TOTAL W/O] * 200) + [Total Sales]) / [Work Days So Far]) * [Work Days]
 ),[Projected Sales])
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    You may modify the calculate column using dax below:

    Currently Projected Sales =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Current Month Sales',
            'Location'[Country Code],
            'Location'[Location Type],
            'Location'[Location Code]
        ),
        "Projected Sales", ( ( ( [TOTAL W/O] * 200 ) + [Total Sales] ) / [Work Days So Far] ) * [Work Days]
    ),
    IF ( 'Location'[Country Code] = "SP", 450000, [Projected Sales] )
)

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Anonymous ,

     

    You may modify the calculate column using dax below:

    Currently Projected Sales =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Current Month Sales',
            'Location'[Country Code],
            'Location'[Location Type],
            'Location'[Location Code]
        ),
        "Projected Sales", ( ( ( [TOTAL W/O] * 200 ) + [Total Sales] ) / [Work Days So Far] ) * [Work Days]
    ),
    IF ( 'Location'[Country Code] = "SP", 450000, [Projected Sales] )
)

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.