Difference between to types in collum with measures

[marcos_osorio]

Hi, I have a question to know if it is possible to do it.

I have a matrix with several measures in the rows, this measures divides in two types, Result and Budget.

Ex:                  Result        Budget

Measure 1

Measure 2

Measure 3

Is there a way to calculate the diff between the two types without making the "Measure 1 result" - "Measure 1 Budget"? so to avoid duplicating all measures?

Thank you very much.

[Dhacd]

Good day @marcos_osorio,
Since you haven't shared any data source and the desired outcome I have assumed one which can be found below.

Source Data:

Expenditure	Status	Amount
Internal	Result	150
Internal	Budget	200
External	Result	250
External	Budget	300

Expected Outcome:

Now, this can be implemented by using a single measure and adding another table to the Power BI.

If you believe this is the desired output, please follow along else please share dummy data for the input and output.

First, you need to load another table as below (This can be implemented using a power query or excel).

Switch Table:

Status	Sort
Result	1
Budget	2
Difference	3

Now we have 2 tables in the data model Source Data and Switch Table,

Add a matrix visual to the page and in the column, headers add expenditure from the Source data table, and to the row, headers add Status from the switch table. Now since there is no relationship between these tables you will be getting an error.

We are not creating any relationship but creating a measure and adding an inbuilt filter context by using a conditional function. (Both if and switch will be working)

Here I used a Switch statement to provide the result.

Values = SWITCH(TRUE(),

                      SELECTEDVALUE('Switch Table'[Status]) = "Result",

                          CALCULATE(sum('Source Data'[Amount]),'Source Data'[Status]="Result"),

                      SELECTEDVALUE('Switch Table'[Status]) = "Budget",

                          CALCULATE(sum('Source Data'[Amount]),'Source Data'[Status]="Budget"),

                      SELECTEDVALUE('Switch Table'[Status]) = "Difference",
                          CALCULATE(sum('Source Data'[Amount]),'Source Data'[Status]="Budget")-CALCULATE(sum('Source Data'[Amount]),'Source Data'[Status]="Result")

)

Now add this measure to the values field in the matrix and you will get the result.

Measure Explanation: The switch statement will be checking the row level of the status in the switch table and according to that the calculate function will be the calculating the sum with the desired filter context from the source table.

And if you think this helps you please mark this reply as an answer so it will be useful for more users.
Thanks and regards,
Atma.

[v-kkf-msft]

Hi @marcos_osorio ,

Could you please share your data, formula and expected output?

Best Regards,
Winniz

[speedramps]

We want to help. Please try give a better decsription. This one looks a bit rushed. ☹️

Provide a copy of input data as a table (not a screen shot) so we can import it and develop a solutuion.

Also provide an example of the desired output with clear description. 

Hide any private date.

Thanks 😀😀