topic Re: Key Figures Sheet - Measures in columns and rows in DAX Commands and Tips

Key Figures Sheet - Measures in columns and rows

M_Chris — Thu, 07 Nov 2024 16:38:21 GMT

Dear all,

I am working with a large and given data model in our company. My target is to create a flexible key figures sheet. Here is a short extract of some columns in the model.

Type of Data	KPI	Value
Forecast1	Revenues	100
Forecast2	Revenues	110
Actuals	Revenues	50
Actuals PY	Revenues	45
Forecast1	Orders	120
Forecast2	Orders	105
Actuals	Orders	95
Actuals PY	Orders	50
Forecast1	Profit	30
Forecast2	Profit	35
Actuals	Profit	25
Actuals PY	Profit	15

I would like to create a measure for each KPI and for each Type of data so that I can flexible calculate with it every month.

The result should look pretty much like this:

	Actuals PY	Actuals	Forecast1	Forecast2	delta to FC PM
Orders
Revenues
Profit
Profit margin%

I was able to generate the KPI's by using an if formula. What would be an efficient way to calculate the type of data measures?

Many thanks for your support!

Re: Key Figures Sheet - Measures in columns and rows

FreemanZ — Thu, 07 Nov 2024 18:32:20 GMT

hi M_Chris ,

as i see, type of data can work well with your KPI measures, what do you mean by "efficient way to calculate the type of data measures"? could you elaborate that?

Re: Key Figures Sheet - Measures in columns and rows

M_Chris — Thu, 07 Nov 2024 18:50:32 GMT

I thought it makes sense to have e.g. a calculated measure for the individual KPI"s. like Revenues or Profit so that I can calculate the Profit margin in % afterwards. Therefore I am looking for the DAX formula to create e.g. a separate Measure to show the Profit in the rows across all type of data. I tried with the calculate fucntion so far.

Re: Key Figures Sheet - Measures in columns and rows

VahidDM — Fri, 08 Nov 2024 01:43:51 GMT

To efficiently create your key figures sheet with KPIs as rows and Types of Data as columns, you can use a dynamic measure within a Matrix visual. Here's how you can achieve this:

Use a Matrix Visual

Rows: Place the KPI field.
Columns: Place the Type of Data field.
Values: Use a dynamic measure (explained below).

Create a Dynamic Measure

This measure will display the correct value based on the KPI and Type of Data in the current context:

KPI Value = VAR CurrentKPI = SELECTEDVALUE('YourTable'[KPI]) VAR CurrentType = SELECTEDVALUE('YourTable'[Type of Data]) RETURN SWITCH( TRUE(), CurrentKPI = "Profit margin%", DIVIDE( CALCULATE( SUM('YourTable'[Value]), 'YourTable'[KPI] = "Profit", 'YourTable'[Type of Data] = CurrentType ), CALCULATE( SUM('YourTable'[Value]), 'YourTable'[KPI] = "Revenues", 'YourTable'[Type of Data] = CurrentType ) ), SUM('YourTable'[Value]) )

Explanation:
CurrentKPI and CurrentType: Capture the KPI and Type of Data in the current cell of the Matrix.
SWITCH Function: Determines what to calculate based on CurrentKPI.

If Profit margin%, it calculates Profit ÷ Revenues for the current Type of Data.
Otherwise, it sums the Value for the current KPI and Type of Data.

3. Calculate Delta to Forecast Previous Month
Add a measure for the "delta to FC PM":

Delta to FC PM = VAR Actuals = CALCULATE( SUM('YourTable'[Value]), 'YourTable'[Type of Data] = "Actuals" ) VAR Forecast1 = CALCULATE( SUM('YourTable'[Value]), 'YourTable'[Type of Data] = "Forecast1" ) RETURN Actuals - Forecast1

Update the Matrix Visual

Columns: Modify your columns to include the calculated "Delta to FC PM" by adding it to your data or creating a custom column in the Matrix.
Values: Ensure the KPI Value measure is used.

Format the Values

Percentage Format: For the Profit Margin %, set the format to percentage.

Right-click the KPI Value measure in the Fields pane.
Choose Format and select Percentage for the Profit Margin% row.

Benefits of This Approach

Dynamic and Scalable: Automatically adjusts for new KPIs or Types of Data without creating multiple measures.
Efficient: Reduces the number of measures needed, simplifying maintenance.
Flexible Calculations: Allows for custom calculations like Profit Margin and Delta within the same measure.

Example Output

KPI	Actuals PY	Actuals	Forecast1	Forecast2	Delta to FC PM
Orders	50	95	120	105	(Actuals - FC1)
Revenues	45	50	100	110	(Actuals - FC1)
Profit	15	25	30	35	(Actuals - FC1)
Profit margin %	33.3%	50%	30%	31.8%	N/A

Notes

Replace 'YourTable' with the actual name of your data table.
Ensure your data model properly reflects relationships if needed.
The Delta to FC PM measure assumes you're comparing Actuals to Forecast1; adjust as necessary.

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

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: Key Figures Sheet - Measures in columns and rows

M_Chris — Fri, 08 Nov 2024 13:04:12 GMT

VahidDM thank you very much. That is exactly what I have been looking for.

It is working out quiet nice. The only thing I am not yet able to get done is the change of the layout so that the margin appears in percentages. I am only able to change the whole set of data to decimal places but not one single row into percentages.

Cheers and really appreciated.

Re: Key Figures Sheet - Measures in columns and rows

VahidDM — Sat, 09 Nov 2024 01:14:52 GMT

Hi M_Chris

You need to update the current switch and add Format function to change the format of data for the items you wanna see them in % format.

Like this:

KPI Value = VAR CurrentKPI = SELECTEDVALUE ( 'YourTable'[KPI] ) VAR CurrentType = SELECTEDVALUE ( 'YourTable'[Type of Data] ) RETURN SWITCH ( TRUE (), CurrentKPI = "Profit margin%", FORMAT ( DIVIDE ( CALCULATE ( SUM ( 'YourTable'[Value] ), 'YourTable'[KPI] = "Profit", 'YourTable'[Type of Data] = CurrentType ), CALCULATE ( SUM ( 'YourTable'[Value] ), 'YourTable'[KPI] = "Revenues", 'YourTable'[Type of Data] = CurrentType ) ), "Percent" ), SUM ( 'YourTable'[Value] ) )

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

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: Key Figures Sheet - Measures in columns and rows

M_Chris — Sat, 09 Nov 2024 18:37:50 GMT

VahidDM great job! I am really impressed. it works awesome. Just one minor thing: currently it shows the percentages with two decimal places. is it possible to include in the format fomular that the percentage should appear with only one decimal place? Of course I will accept this as solution. really appreciated....

Re: Key Figures Sheet - Measures in columns and rows

VahidDM — Sun, 10 Nov 2024 08:04:27 GMT

Hi M_Chris

Try this:

KPI Value = VAR CurrentKPI = SELECTEDVALUE ( 'YourTable'[KPI] ) VAR CurrentType = SELECTEDVALUE ( 'YourTable'[Type of Data] ) RETURN SWITCH ( TRUE (), CurrentKPI = "Profit margin%", FORMAT ( DIVIDE ( CALCULATE ( SUM ( 'YourTable'[Value] ), 'YourTable'[KPI] = "Profit", 'YourTable'[Type of Data] = CurrentType ), CALCULATE ( SUM ( 'YourTable'[Value] ), 'YourTable'[KPI] = "Revenues", 'YourTable'[Type of Data] = CurrentType ) ), "0.0%" ), SUM ( 'YourTable'[Value] ) )

Hope this solve your problem and please accept all 3 version of my post as solutions to help others.

Appreciate your Kudos!!

LinkedIn | Twitter | Blog | YouTube

Re: Key Figures Sheet - Measures in columns and rows

M_Chris — Tue, 12 Nov 2024 12:26:56 GMT

Hi all,

VahidDM the solution forks very well so far. I was able to create a first key figures sheet. Currently I added a separate column for sorting/ranking reasons of my KPI's which I can change manually in the background. When adding another "Ranking" column my calculcated margins disappear.

BEFORE:

AFTER (without margins)

I do not understand why the calculcated margins are disappearing because I thought I linked them between KPI and Data Type.

KPI Value =

VAR CurrentKPI = SELECTEDVALUE(EPM[KPI])

VAR CurrentType = SELECTEDVALUE(EPM[Data Type])

RETURN SWITCH(TRUE(),

CurrentKPI = "Gross profit %", FORMAT(

DIVIDE(

CALCULATE(SUM(EPM[Value]), EPM[KPI] = "Gross profit",EPM[Data Type] = CurrentType),

CALCULATE(SUM(EPM[Value]),EPM[KPI] = "Revenue", EPM[Data Type] = CurrentType)), "0.0%"),

SUM(EPM[Value]))

Do you have any idea with the margins are disappearing when I add the additional ranking column as first column? Any idea how to adjust the DAX?

Many thanks

Chris