topic Re: % compared to 1 row in Matrix in DAX Commands and Tips

% compared to 1 row in Matrix

Anonymous — Mon, 24 Feb 2020 17:41:38 GMT

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.

Re: % compared to 1 row in Matrix

Anonymous — Mon, 24 Feb 2020 17:50:30 GMT

[% of Production] = var __production = CALCULATE( [Measure], Table[Branch] = "production" ) var __current = [Measure] return DIVIDE(__current, __production)

You have not given us the names of the tables and columns... so please figure out the generics by yourself.

Best

Re: % compared to 1 row in Matrix

Anonymous — Mon, 24 Feb 2020 18:29:10 GMT

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

Re: % compared to 1 row in Matrix

Anonymous — Mon, 24 Feb 2020 18:44:37 GMT

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

Re: % compared to 1 row in Matrix

Anonymous — Mon, 24 Feb 2020 19:07:34 GMT

The measure works perfectly OK. [Total Amount] is the sum of the Amount column in the Transaction table you see below.

Re: % compared to 1 row in Matrix

Anonymous — Mon, 24 Feb 2020 19:46:44 GMT

Hi Darlove,

I have re-created it and tested. You are right. It does work, but when I create the matrix from a categorie table it does not work. I Have made an example pbx file to show you and am trying to get it up here.

Re: % compared to 1 row in Matrix

Anonymous — Mon, 24 Feb 2020 19:54:30 GMT

Zie the file
Example PBX

Re: % compared to 1 row in Matrix

Anonymous — Tue, 25 Feb 2020 10:03:23 GMT

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

Re: % compared to 1 row in Matrix

Anonymous — Mon, 02 Mar 2020 07:33:40 GMT

Thanks for your answer.

you are a strickt teacher 😉

Re: % compared to 1 row in Matrix

Anonymous — Mon, 02 Mar 2020 09:11:55 GMT

Anonymous, which teacher would you rather have: one that smiles at you constantly, cuddles up to you and knows about the subject just a tiny bit... or rather one that is strict but from which you can truly learn the art? Think about martial arts - the best masters are the ones that require of you a superhuman effort 🙂

Best
D