Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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.
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
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.
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
...
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |