How to subtract a value from a specific measure in a table, but not subtract from other measures

[sergiodefreitas]

Hello Power BI community,

I need to subtract a value of 17.11 from a specific measure I have within a table for a specific month and document number, but I do not want this value subtracted from other measures or months / document numbers that I have within the same DAX formula.

Below is a copy of my DAX formula:

CALCULATE([VendasLIQ], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"julho"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcdoccab'[Anulado] IN { 0 })

-

CALCULATE([VendasLIQ], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"julho"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcdoccab'[Anulado] IN { 0 }, 'wgcDocLinhas'[Numdoc] IN { 16 }, 'wgcDocLinhas'[Tipodoc] IN { "NC" })

-

CALCULATE([IVANEG], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"julho"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcDocLinhas'[Numdoc] IN { 16 }, 'wgcDocLinhas'[Tipodoc] IN { "NC" })

+

CALCULATE([DevLIQ-IVA]-17.11, 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"julho"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcDocLinhas'[Numdoc] IN { 16 }, 'wgcDocLinhas'[Tipodoc] IN { "NC" })

-

CALCULATE([IVANEG], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"julho"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcDocLinhas'[Numdoc] IN { 14 }, 'wgcDocLinhas'[Tipodoc] IN { "FA4" })

+

CALCULATE([VendasLIQ], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"agosto"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcdoccab'[Anulado] IN { 0 })

-

CALCULATE([VendasLIQ], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"agosto"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcdoccab'[Anulado] IN { 0 }, 'wgcDocLinhas'[Numdoc] IN { 11 }, 'wgcDocLinhas'[Tipodoc] IN { "NC" })

+

CALCULATE([DevLIQ], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"agosto"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcDocLinhas'[Numdoc] IN { 11 }, 'wgcDocLinhas'[Tipodoc] IN { "NC" })

-

CALCULATE([VendasLIQ], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"agosto"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcdoccab'[Anulado] IN { 0 }, 'wgcDocLinhas'[Numdoc] IN { 12 }, 'wgcDocLinhas'[Tipodoc] IN { "NC" })

+

CALCULATE([DevLIQ], 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[Mês] IN {"agosto"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcDocLinhas'[Numdoc] IN { 12 }, 'wgcDocLinhas'[Tipodoc] IN { "NC" })

At the moment I am able to subtract the value of 17.11 from the month of July, year 2021, and document number 16, however, the DAX formula as it is written right now, also subtracts the value of 17.11 from the other month which I have in my formula, which is August.

I want to include the DAX expression for the month of August within the same DAX formula, however I only want the value of 17.11 subtracted for the month of July, year 2021, and document number 16, for document type "NC".

Can anyone please help me with this, and explain what I am doing wrong in the DAX formula above.

Thank you for your attention regarding this matter,

Kind Regards,

Sergio Miguel De Freitas.

[Anonymous · Accepted solution]

Hi @sergiodefreitas ,

According to your description, you want to subtract 17.11 in a specific case, you can see in your measure that you subtracted the value from your measure under your judgment. But you say "also subtracts the value of 17.11 from the other month which I have in my formula, which is August".
This situation needs to be combined with the fields you place on top of the visual.
The measure is calculated as the result under the current filter context, and you can use your measure:
CALCULATE([DevLIQ-IVA]-17.11, 'wgcDocLinhas'[Serie] IN { 21 }, 'wgcDocLinhas'[Data].[ Mês] IN {"julho"}, 'wgcterceiros'[TipoDoente] IN { "GRUPO" }, 'wgcDocLinhas'[Numdoc] IN { 16 }, 'wgcDocLinhas'[Tipodoc] IN { "NC" })
Test in your visual that the value he returns is the same as you expected.

Refer this docunment , you can store the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.

And for your needs, you can also provide your sample data, please pay attention to protect private data, and give us the sample data you want in the form of a table, so that we can better help you.

Best Regards,

Neeko Tang

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