locked
PowerPivot Question - Create measure that returns a subtotal (?) RRS feed

  • Question

  • Hi everyone, new to PowerPivot here :)

    I'm working on a measure which I'd like to use in PowerView, my data model contains 2 things:

    1) Employees' scores

    2) Stores in which employees work in

    I would like to be able to put the employee's score in comparison to the store average (which is the average of all the employee's scores in the store), and then provide every employee with an individual report on their scores against their store's score (by tiling the view in PowerView by Employee). To do this, my (first attempt) at a measure for the store average looks like the following:

    Overall_Avg:=CALCULATE([Score], ALL(Employee[Name]))

    which gives me the score of the employee vs. all other employees across all stores. What I'd like to then achieve is to add an additional filter that filters only the scores for the store that the employee belongs to - which is where I'm stuck. Here are some of my attempts at them

    Store_Avg:=CALCULATE([Score], ALL(Employee[Name]), FILTER(Employee[Store] = Store[Store Name]))

    returns an error for me. However, trying:

    Store1_Avg:= CALCULATE([Score], ALL(Employee[Name]), FILTER(Employee[Store] = "Store 1"))

    works perfectly, but doesn't give me the desired output, which is 1 measure that changes the store depending on which employee (and thus store) is being selected.

    Any help would be appreciated :)

    Thanks!

    Thursday, June 25, 2015 2:05 AM

Answers

  • So an alternate approach taken from here is https://www.sqlbi.com/articles/ratio-over-subtotals-with-normalized-tables-in-dax/ 

    Store Sales:=CALCULATE([score],all( <Your fact table name> ),values(Employees[StoreName]))


    http://darren.gosbell.com - please mark correct answers

    Sunday, June 28, 2015 9:37 PM

All replies

  • Try using ALLEXCEPT .

    eg.

    CALCULATE([Score], ALL(Employee[Name]), ALLEXCEPT(Employee, Employee[Store]))


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Friday, June 26, 2015 7:25 AM
    Friday, June 26, 2015 6:44 AM
  • Hi Darren,

    Thanks for the quick response - the formula above gives the same result as CALCULATE([Score], ALL(Employee[Name])).

    Have also tried the following: CALCULATE([Score], ALLEXCEPT(Employee, Employee[Store])) - doesn't apppear to work either, gives the same result as the above.

    Thanks for the suggestion - any other approaches that I could try?

    Sunday, June 28, 2015 8:53 AM
  • So an alternate approach taken from here is https://www.sqlbi.com/articles/ratio-over-subtotals-with-normalized-tables-in-dax/ 

    Store Sales:=CALCULATE([score],all( <Your fact table name> ),values(Employees[StoreName]))


    http://darren.gosbell.com - please mark correct answers

    Sunday, June 28, 2015 9:37 PM
  • Excel 2010 with free PowerPivot Add-In.
    Compatible with Office 2013 Pro Plus.
    Compare scores (on a curve)
    Guessed wildly on your database format,
    so share your file if need be.
    http://www.mediafire.com/view/onpnmi3xvxf2a1z/06_28_15.xlsx

    Sunday, June 28, 2015 9:38 PM
  • That's brilliant - it works! Just want to add an additional note to the above - if you want to further break the comparison value down into other categories (e.g. splitting up by different item categories that make up the overall score), you'll have to add similar additional filters, else the measure will return the same value across all the categories

    e.g.

    Store Sales:=CALCULATE([score],all( <Your fact table name> ),values(Employees[StoreName]), values(Products[ItemCategory]), ...)

    Thanks so much!

    Tuesday, June 30, 2015 5:10 AM