How to calculate values with different formula for each row?

[Robin9700]

Hi All,

I would like to (manually) add a table to my dataset which calculates different values, with different criteria for each row. What I need is a simple table, with 9 rows and 2 columns, looking like this (simple example):

Label                     Calculated value
Labour costs         Calculate ( SUM( [table 1]column 1 , [table1]column 2 = 4000)
Ads/marketing      Calculate ( SUM( [table 1]column 1) , Filter([table1]column 2 = 4100), Filter([table1]column 2 = 4110) )
IT-dept.                 Calculate ( SUM( [table 1]column 1) , Filter([table1]column 2 = 1410), Filter([table1]column 2 = 2500) )
etc.

Is it at all possible to calculate values like this? When adding a column or measure, I only see the possibility to apply 1 formula to all rows, but obviously that is not what I want. Another possibility is creating 9 mini-tables using aforementioned solution, but there has to be a better way, right?

Any advice would be appreciated!

PS please note that table1 is a different table. There is no relationship possible between these tables.

[v-luwang-msft · Accepted solution]

Hi @Robin9700 ,

In my opinion，you need to create a new label table :

And if you want to create a new column,test like below:

newcolumn=if([Label[label]="Labour costs",
               Calculate ( SUM( [table 1]column 1 , [table1]column 2 = 4000),
          IF([Label[label]="Ads/marketing",
Calculate ( SUM( [table 1]column 1) , Filter([table1]column 2 = 4100),Filter([table1]column 2 = 4110) ),
       IF([Label[label]="IT-dept.", Calculate ( SUM( [table 1]column 1) , Filter([table1]column 2 = 1410), Filter([table1]column 2 = 2500)),BKANK()))

And if you want to create a new measure,test like below:

newcolumn=if(max([Label[label])="Labour costs",
               Calculate ( SUM( [table 1]column 1 , [table1]column 2 = 4000),
          IF(max([Label[label])="Ads/marketing",
Calculate ( SUM( [table 1]column 1) , Filter([table1]column 2 = 4100),Filter([table1]column 2 = 4110) ),
       IF(max([Label[label])="IT-dept.", Calculate ( SUM( [table 1]column 1) , Filter([table1]column 2 = 1410), Filter([table1]column 2 = 2500)),BKANK()))

And if question still not solved ,could you pls share your sample data for [table1] and [table 1],and expect output result?

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[DataInsights]

@Robin9700,

You can achieve this with a disconnected table (no relationships). Use "Enter data" to manually input the data. The Index column is used for sorting in a visual. Example:

Create measures for each label (9 measures). Then, create a SWITCH measure that uses the appropriate measure.

Label Measure =
SWITCH (
    SELECTEDVALUE ( Labels[Label] ),
    "Labour costs", [Labour costs],
    "Ads/marketing", [Ads/marketing]
)

In a matrix visual (for example), use Labels[Label] as rows, and [Label Measure] as values.

[TheoC]

Hi @Robin9700 

If you create a measure for each row in your post, you can then title the measures exactly what you have in your column 1. Drag the measures into a Matrix visual (into the Value field), then turn on the option "Show on Rows".

https://powerbidocs.com/2020/10/09/show-values-on-rows-for-matrix-visual/ 

This will give you an output exactly as you require. 

I hope this is what you're searching for.

All the best.

Theo