Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BobKoenen
Helper IV
Helper IV

% 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 productionYTD% in relation to production
Production1000100%2000100%
Machine cost505%753,75%
Personel cost25025%100050%
Other cost10010%20010%

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. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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

Hi Sorry for the incomplete information. 

Unfortunatly the sollution does not work. It gives me 

 This month% in relation to production
Production1000100%
Machine cost50 
Personel cost250 
Other cost100 

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. 

production101-1-2020
Production151-1-2020
machine cost 41-1-2020
machine cost 415-2-2020
Other Cost65-3-2020
etc 

 

Anonymous
Not applicable

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

 

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. 

Zie the file 
Example PBX 

 

 

Anonymous
Not applicable

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

Thanks for your answer. 

you are a strickt teacher 😉

Anonymous
Not applicable

@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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors