How to replace a row in a table with a metric?

[Anonymous]

Hello all, I have a table of data like this, how can I replace the 2 empty cells with metric?

Data:

Metric	Result
Profit before tax	100
Tax	Profit before tax * 0.2
Profit after tax	Profit before tax - Tax

Expected Result

Metric	Result
Profit before tax	100
Tax	20
Profit after tax	80

So far I have tried to build this metric:

Result:= SWITCH(SELECTEDVALUE('Test'[Metric]),
"Tax",Profit before tax * 0.2,
"Profit after tax",Profit before tax -Profit after tax,
Sum(Result))

[MFelix]

Hi @Anonymous ,

One question about your data is you Profit Before Tax a column or a calculated measure in the model? 

Assuming that the the Profit before tax is the SUM of a certain column in the model you should redo your measure to something like this:

Result :=
VAR Profit_Before_tax =
    SUM ( Table[Column] )
VAR Tax_Amount =
    SUM ( Table[Result] ) * 0.2
VAR Profit_after_tax = Profit_Before_tax - Tax_Amount
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Test'[Metric] ),
        "Tax", Tax_Amount,
        "Profit after tax", Profit_after_tax,
        PRofit_Before_tax
    )

Without any other specification about your data is difficult to give you a better answer can you please share a sample of your data and expected results if the above measure does not work.

Regards,

MFelix

[Anonymous]

@MFelix Hi Felix,

The Profit before tax was indeed a sum measure of an Amount column from SQL Database source ( an SQL Database Table).

What I precisely want to ask is: Is there any way to put many measures in the same table? where one column will be measure name, and the other will be measure Value.

Here is the link to a pseudo data

https://drive.google.com/file/d/1-fIO_lxvt6DHkhBttqcLx2oMz6tEpddI/view

[MFelix]

Hi @Anonymous ,

Sorry for the late response you have 2 options and both are based on creating 3 measures (just to simplify the visualizaiton of the calculations):

Profit before tax = SUM('Profit Before Tax'[Value])

Tax = 'Profit Before Tax'[Profit before tax] * 0,2

Profit after tax = 'Profit Before Tax'[Profit before tax] - 'Profit Before Tax'[Tax]

Option 1:

Add the measure to a matrix table and the go to the options of the matrix table and turn on the option measures on rows

Option 2:

Create  table similar to the one below:

Measure               ID

Profit before tax	1
Tax	2
Profit after tax	3

ID column used for sorting. 

Add the Measure from this table on the rows of a matrix and create the following measure to use on values:

Measures calculation =
SWITCH (
    SELECTEDVALUE ( 'Measures_table'[Measure] );
    "Profit before tax"; [Profit before tax];
    "Tax"; [Tax];
    "Profit after tax"; 'Profit Before Tax'[Profit after tax]
)

You can also replace the measure on the switch formula above by the syntax of each formula and then there is no need to have the 4 measures but only one.

Check PBIX file attach.

Regards,

MFelix