Forum Discussion

ivan_larson_cki's avatar
ivan_larson_cki
Helper III
6 years ago

Display Measure Value Once in Matrix

I have a matrix that breaks out an Amount value over several columns, and the amount info is parsed out by several Row values. I need to insert after the last of those Row values a proportion that breaks down the proportion accounted for by that Row value.

 

So in this Matrix, I'd like to show the proportion of foods (regardless of year) out of the total for each employee. E.g. for Dana, you'd see the proportion of burgers out of all foods, proportion of fires out of all foods, etc. This needs to be displayed before the breakout of amounts by year.

 

Is this doable? I know how to create a measure to calculate the value using CALCULATE, SUM, and ALLEXCEPT, but I can't display that Measure in the matrix unless I want it broken out by year along with the amounts. I know I can work around this limitation if I have separate values in my dataset for each year's amount, but would like to avoid doing that as then all years will have to be displayed regardless of other slicers I apply.

 

In the data, Account Manager on Table1 relates to Employee on Table2.

 

Need Help

2 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi ivab, 

    Below is my design:

    table1

    Account Manager Amount Food Year
    a 10 pie 2017
    a 20 pizza 2017
    a 30 burger 2017
    a 50 pie 2018
    a 20 pizza 2018
    a 5 burger 2018
    a 10 pie 2019
    a 3 pizza 2019
    a 1 burger 2019
    b 40 pie 2017
    b 20 pizza 2017
    b 10 burger 2017
    b 50 pie 2018
    b 20 pizza 2018
    b 30 burger 2018
    b 10 pie 2019
    b 5 pizza 2019
    b 10 burger 2019

    Table2

    employ hire year
    a 2005
    b 2006

    relationship between to table is one-many

    Then you could ceate measure like below

    Measure =
SUM ( table1[Amount] )
    / CALCULATE (
        SUM ( table1[Amount] ),
        FILTER (
            ALLEXCEPT ( table1, table1[Year] ),
            table1[Account Manager] = MIN ( Table2[employ] )
        )
    )

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ivan_larson_cki's avatar
      ivan_larson_cki
      Helper III
      6 years ago

      Thanks. Is there a way to do this that doesn't require having the measure broken out by year? I just want to show the overall proportion, but still have the amount data broken out by year.