How to replace a field value with a measure in a table visual?

[rsouza023]

See the example below:

I have a Revenue, Cost and Margin, and I want the Margin % value to come next. As this Margin% value is relative, it cannot be in the dataset, it must actually be a measure.

How can I replace the value when the selected field is equal to Margin%?

Below are two measurements. One doing the Margin calculation, that is, dividing margin by revenue. And on the other trying to use SELECTEDVALUE+SWITCH to replace the value when the MARGIN% field is used. The result didn't work.

MARGEM = 
VAR RECEITA = CALCULATE(SUM(TESTE2[VALOR]), FILTER(VALUES(TESTE2[RUBRICA]), [RUBRICA] = "RECEITA"))
VAR MARGEM = CALCULATE(SUM(TESTE2[VALOR]), FILTER(VALUES(TESTE2[RUBRICA]), [RUBRICA] = "MARGEM"))
RETURN
MARGEM/RECEITA


Medida = 
VAR SEL = SELECTEDVALUE(TESTE2[RUBRICA])
RETURN
SWITCH(TRUE(), SEL = "MARGEM %", [MARGEM], 0)

The expected result would be:

RUBRICA	VALOR
RECEITA	100
CUSTO	-30
MARGEM	70
MARGEM %	70%

[Icey · Accepted solution]

Hi @rsouza023 ,

Try this:

VALOR Measure =
SWITCH (
    SELECTEDVALUE ( TEST2[RUBRICA] ),
    "MARGEM %",
        VAR RECEITA_ =
            CALCULATE ( SUM ( TEST2[VALOR] ), TEST2[RUBRICA] = "RECEITA" )
        VAR MARGEM_ =
            CALCULATE ( SUM ( TEST2[VALOR] ), TEST2[RUBRICA] = "MARGEM" )
        RETURN
            FORMAT ( DIVIDE ( MARGEM_, RECEITA_ ), "##.00#%" ),
    SUM ( TEST2[VALOR] )
)

Best Regards,

Icey

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

[amitchandak]

@rsouza023 , Revenue, Cost and Margin are measures then you can also have measure Margin % and show all of them in matrix and use show on Row

If these are row values and Margin % is a measure then check for Hybrid display

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

[rsouza023]

@amitchandak 

Thanks for the answer.
In my case it is in fact a hybrid table.
The focus is to build a balance sheet, so the Margin measurements would come right after the nominal margin value.

I'm going to vote for the hybrid solution and try to replicate the solution you shared as a stopgap.