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

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

 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Zie the file 
      Example PBX 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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