Substract a number with conditions from a measure

[Fran2306]

Hello,

I need to substract a "virtual sale" (a number) from my Sales measure, because it should not be shown in the total,

The Sales measure:

Sales MTD:=CALCULATE([Sales];CALCULATETABLE(DATESMTD('Sales calendar'[order date])))

and I need to substract USD 140 from this calculation BUT only when an specific cost center and period is applied.

I tried to substract with the IF formula to filter this conditions but it doesn't work.

Please help

[Mrxiang]

To subtract a virtual sale from your Sales measure, you can use the following formula in your CALCULATE statement:
Sales MTD:=SUM(IF([Measures]="Sales";[Cost Centers]="Cost Center 1" AND [Periods]="Period 1");[Sales])

This formula first filters for the rows that meet the criteria of having a "Sales" measure and having either the "Cost Center 1" or "Period 1" measure values. Then, it calculates the sum of the sales for those rows, but only for the rows where the measure is "Sales".
To further filter for virtual sales, you can add another filter to the formula using the "virtual sales" calculation. To do this, you would use the following formula:
Virtual Sales MTD:=SUM(IF([Measures]="Sales";[Cost Centers]="Cost Center 1" AND [Periods]="Period 1");[Virtual Sales])

This formula uses the "Virtual Sales" calculation, which is available in Microsoft Analysis Services, to calculate the sum of virtual sales for the selected cost center and period.
Finally, you can add the two calculated measures together to get the final result:Sales MTD:=SUM(IF([Measures]="Sales";[Cost Centers]="Cost Center 1" AND [Periods]="Period 1");[Sales])-SUM(IF([Measures]="Virtual Sales";[Cost Centers]="Cost Center 1" AND [Periods]="Period 1";[Virtual Sales])

This formula uses the SUMIF function to combine the two subtotals and produce the final result.
I hope this helps! Let me know if you have any questions or need further assistance.

[Anonymous · Accepted solution]

Hi @Fran2306 ,

To subtract a "virtual sale" from your Sales measure, you can use the following formula:

Sales MTD - Virtual Sale :=
CALCULATE (
    [Sales MTD],
    FILTER (
        'Sales calendar',
        'Sales calendar'[Cost Center] <> "Specific Cost Center"
            || 'Sales calendar'[Period] <> "Specific Period"
    )
) - 140

This formula first calculates the Sales MTD measure, then filters the 'Sales calendar' table to exclude rows where the Cost Center is "Specific Cost Center" and the Period is "Specific Period". Finally, it subtracts 140 from the result.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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