Forum Discussion
% compared to 1 row in Matrix
Hi People,
I am trying to create a P&L with a column that shows the % of production compared to the other rows in the matrix.
| This month | % in relation to production | YTD | % in relation to production | |
| Production | 1000 | 100% | 2000 | 100% |
| Machine cost | 50 | 5% | 75 | 3,75% |
| Personel cost | 250 | 25% | 1000 | 50% |
| Other cost | 100 | 10% | 200 | 10% |
I tried to use All to get an absolute value but this does not work because i Need to be able to filter on month and with my All formula I only was able to get the grand total of the whole year.
Hope you can help me.
Mate,
First of all, please let these golden rules of data modeling sink in well:
Fact tables should never be exposed to the user. NEVER, EVER. All columns in them MUST be hidden unconditionally. Slicing is only ever done through dimensions.
Second of all, here's the measure:
% prod 2 = var __current = [Sum amount] var __prod = CALCULATE( [Sum amount], categories[Group] = "production", all(categories) ) return DIVIDE(__current, __prod)I think this is what you've been looking for...
For the future. Please try to learn as much as possible about good design and data modeling practices. It'll help you avoid pitfalls and production of numbers you will not be able to explain. Just a piece of good advice from a BI old-timer. You'll thank me later.
Best
D
9 Replies
Hi Sorry for the incomplete information.
Unfortunatly the sollution does not work. It gives me
This month % in relation to production Production 1000 100% Machine cost 50 Personel cost 250 Other cost 100 I have created a Measure for this Month which is very simple =sum(transactiontable[amount]
I have also created a production measure =Calculate(sumx(transactiontable[amount]; transactiontable[description = "production"
I keep running into the problem that the other rows in the matrix are based on the other descriptions and therefore I cannot get "production"into that row.
The data comes out of a large transactional table. like this
Hope this clears it up.
production 10 1-1-2020 Production 15 1-1-2020 machine cost 4 1-1-2020 machine cost 4 15-2-2020 Other Cost 6 5-3-2020 etc Hi there.
First, I'd advise you should create correct models. A correct model is a star schema (or snowflake if star can't be achieved). Second, fact tables should only store numbers to aggregate and keys to dimensions. All columns in fact tables must be hidden without exception. Third, all time-based analysis must be carried out through proper Data tables. Then and only then will you be able to write correct and SIMPLE DAX against your model. Otherwise... be prepared to produce numbers you will not be able to understand and even know you're producing falsehood.
My formula certainly does what it should on a proper model. I'd like to see your model and data to find the reason why it does not give you what you want. Can you please share a file with some sample data and tell us what the results should be for the data?
Thanks.
Best
D