Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

% 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 t...
DAX
DAX Calculation
percentage
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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