Group value by Elements of another table

[herbemischung]

Hi, 

my structure lookl like this:

In the Table "Kontenzuordnungen" there is an account number "Kontonr.". Every Accoutnumber has a category "Kontenkategorie" like earnings, variable costs fixed costs and so on...

The values are in the Table "Kontoexport Forst" in "Betrag".

What I'm trying to achieve is to create measures that calculate with the Elements of Kontenkategorie.

for instance: contribution margin = earnings + variable costs [this is correct because the values are negative]

it would also work if I could say: take the value for each accountnr. where the "Kontenkategorie" is earnings and variable costs.

i tried that with calculate and related, but I failed miserably 😕 Maybe there is someone who can help me out?

best regards!

[BhaveshPatel]

Can you please clearly explain what is required as it seems quite vague. Posting of sample file would help us to design a solution for you if it is permitted.

[herbemischung]

okay, I try to be more specific:

in the end there should be a diagram like:

i hope its clear now what I am trying to do.

[v-shex-msft]

Hi @herbemischung,

Based on your description, you want to group the records based on the related column names,right?

If this is a case, you can refer to below steps to get the result:

1. Add a calculate column to table B which get the related account category.

Category= Related(TableA[account category])

2. Summary tableB.

Summary= Distinct(Summarize(TableB,[Category],"TotalValue",SUM([value])))

3. Write measures to get the value of specific category.

Measure= lookupvalue(Summary[TotalValue],Summary[Category],"Variable costs")

Regards,

Xiaoxin Sheng

[herbemischung · Accepted solution]

Thank you all for your help!

I followed your lead, but I only got one value for all years. 

The solution that worked for me was the following:

I created a new Table (GuV) that looks like this:

GUV-Cat                                    Category

Contribution Margin                 Earnings

Contribution Margin                 Variable Costs

Company Result                        Earnings

Company Result                        Variable Costs

Company Result                        Labor Costs

Company Result                        Fixed Costs ..... and so on

Earnings                                    Earnings

Variable Costs                           Variable Costs

...

And then I created a second GuV Table (GuV_sort)

GuV-Cat                      Sort

Earnings                      1

Variable Costs             2

Contribution Margin   3

Labor Costs                 4

Fixed Costs                  5

...                                 ...

Company Result        10

and sorted the GuV-Cat by collum Sort

So I was able to get the structure and the data like:

GuV                                 2010   2011   2012

Earnings                          500     600     700

Variable Costs                 200     250     300

Contributions Margin     300     350     400

...

But there was also the "easy" way to make a measure like:

Variable Costs = calculate(Sum('Table B'[Value]; Table A[Account Category]="Variable Costs")

The only Problem with this Measure is, that each measure is a separate Value and I wasnt able to create a list like above.

It was like

             Earnings      Variable Costs     CM

2010     500              200                      300

2011     600              250                      350

...

But nevertheless I was able to create the Diagram with theses measures. Which is also possible by using the GuV Table so I don't really need a separate Measure for each Account Category.

[herbemischung]

Thank you all for your help!

I followed your lead, but I only got one value for all years. 

The solution that worked for me was the following:

I created a new Table (GuV) that looks like this:

GUV-Cat                                    Category

Contribution Margin                 Earnings

Contribution Margin                 Variable Costs

Company Result                        Earnings

Company Result                        Variable Costs

Company Result                        Labor Costs

Company Result                        Fixed Costs ..... and so on

Earnings                                    Earnings

Variable Costs                           Variable Costs

...

And then I created a second GuV Table (GuV_sort)

GuV-Cat                      Sort

Earnings                      1

Variable Costs             2

Contribution Margin   3

Labor Costs                 4

Fixed Costs                  5

...

[BhaveshPatel]

You can use Left Outer join in the Query Editor to get the values from your accounts table using Merge Queries ( Table A and Table B)  and then use CALCULATE & SUM Statement to find the sum of all the variable costs or You can use the GROUPBY Statement in Query Editor to achieve the sum of the particular rows you are interested in.

Hope this makes sense to you.