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

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

Thanks for your answer. 

you are a strickt teacher 😉

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