% compared to 1 row in Matrix

[BobKoenen]

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. 

[Anonymous]

[% 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

D

[BobKoenen]

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

[Anonymous]

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

[BobKoenen]

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. 

[BobKoenen]

Zie the file 
Example PBX 

[Anonymous · Accepted solution]

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

[BobKoenen]

Thanks for your answer. 

you are a strickt teacher 😉

[Anonymous]

@BobKoenen, 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

[Anonymous]

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