cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sushicat_san Advocate II
Advocate II

Calculated Measure Based on Criteria - Aggregation Issue?

Hi All, 

 

I think I have an issue with the aggregation of my data and I know it's because of my unfamilarity with DAX.

 

Here's the situation, I need to create a goal metric based off of my past year's sales but only for two functional groups in my company. I can successfully build the formula, but when I drag all of my functional groups to the view with my goal metric the measure is the same for all of my functional groups ("2015 IC Sales Goal" below).

 

Here's a screenshot:

Capture.JPG

 

Here's the formula that I used, which is obviously wrong:

2015 IC Sales Goal = 
CALCULATE ([FY2014 SALES($)]*1.12, 
      OR('MyTable' [Functional Group] = "IC - CENTRAL AND WEST",
             'MyTable'[Functional Group] = "IC - NORTHEAST AND  SOUTHEAST"))

Any help would be appreciated! And if you can find a post to link to the answer of WHY I would greatly appreciate it! I'd like to learn how to fish, so to speak.

1 ACCEPTED SOLUTION

Accepted Solutions
greggyb Resident Rockstar
Resident Rockstar

Re: Calculated Measure Based on Criteria - Aggregation Issue?

  1. This isn't a problem with the last year logic in my original reply. Just change the name of DimDate to the appropriate table.
  2. Really, a goal should be precomputed. In your original question it was simple enough to not care much about how to implement as a measure, but with the details you're revealing, I'd suggest not calculating this as a measure. Your sales last year are known at the time of model refresh, and will never change. These are two defining attributes of values that should be pre-computed. I'd recommend doing this in ETL, either in your source system if you have access/authority to initiate those changes, or as a part of your Power Query step before loading this data to the model. See a sample below for how you might implement this:

Here you go, a nice sample. You can manually enter a multiplier table ('Enter Data') and then copy your sales query and join in the multipliers for each year, then do your multiplication. The result is a FactGoal table that you can then just have some simple measures defined against. This is how we'll set up this sort of reporting for any of our clients.

View solution in original post

6 REPLIES 6
greggyb Resident Rockstar
Resident Rockstar

Re: Calculated Measure Based on Criteria - Aggregation Issue?

Sales =
SUM( 'MyTable'[SalesAmount] )

SalesLastYear =
CALCULATE(
    [Sales]
    ,FILTER(
        ALL( DimDate )
        ,DimDate[FiscalYear] = MAX( DimDate[FiscalYear] ) - 1
    )
)

SalesGoal = 
[SalesLastYear] * 1.12

SalesGoalConditionalDisplay =
SUMX(
    VALUES( 'MyTable'[Functional Group] )
    ,('MyTable'[Functional Group] = "IC - CENTRAL AND WEST"
        || 'MyTable'[Functional Group] = "IC - NORTHEAST AND SOUTHEAST"
    ) * [SalesGoal]
)

This works if you've got a good date dimension (google "power pivot date dimension" for a lot more thorough coverage of the how and why for a date dimension - this conversation is not worth having if you don't have one).

 

We've got a little bit of trickiness in the SUMX(), but its a general good pattern to learn.

 

SUMX() will create a row context based on the table passed to it as argument1 by iterating over the rows of that table. It evaluates an expression (argument2) for each row context, and aggregates these with a sum.

VALUES() returns a table of the distinct values in the column or table named as its only argumnet, based on current filter context. In any visual (even tables / matrices, where we see rows), the labels are filter context. Thus, when only one label is in context (like in a table visual's detail rows), we get a 1-row table returned from VALUES(), and the SUMX() in that case is the same as just evaluating the expression in its argument2.

 

The funkiness we bring is by doing arithmetic with a Boolean value. We perform a Boolean test in the parentheses, checking for the logical or of [Functional Group] being one of the values you're interested in. This returns true or false. False * <numeric expression> = blank. True * <numeric expression> = <numeric expression>. Thus we'll only get [SalesGoal] if the row context in SUMX() is for one of the two names rows.

 

When only one row is in context we get [SalesGoal] for that row if that row context is for one of the named [Functional Group]s.

 

When we get to the total, there will be two row contexts that have non-zero results (the two named items) and those will be aggregated with a sum. This gives us the appropriate total behavior for this measure.

sushicat_san Advocate II
Advocate II

Re: Calculated Measure Based on Criteria - Aggregation Issue?

Thanks for the reply! I'll try it as soon as I'm on the office. You're 2 for 2 on my help topics so far!
sushicat_san Advocate II
Advocate II

Re: Calculated Measure Based on Criteria - Aggregation Issue?

Hi Greg,

 

Thank you again for your help! I definitely forgot to mention a couple of key points in my data.

 

#1 - The database that I use doesn't have any conventional Date Time field. In fact it has absolutely no mention of dates besides the Fiscal Year dim.  For this instance it's actually not that bad of a deal because I don't have to use a date math function to figure my fiscal year sales.

 

#2 - The sales goal is different for each functional group. The IC functional group has a sales goal of 12%, but the others might have a different goal. So when creating my 2015 Sales goal I can't use a blanket [SalesLastYear]*1.12.

 

How would I go about creating the sales goal measure for only my IC functional groups? In Excel I would just use an IF function, but I couldn't get that to work.

 

Thank you again for your help!

greggyb Resident Rockstar
Resident Rockstar

Re: Calculated Measure Based on Criteria - Aggregation Issue?

  1. This isn't a problem with the last year logic in my original reply. Just change the name of DimDate to the appropriate table.
  2. Really, a goal should be precomputed. In your original question it was simple enough to not care much about how to implement as a measure, but with the details you're revealing, I'd suggest not calculating this as a measure. Your sales last year are known at the time of model refresh, and will never change. These are two defining attributes of values that should be pre-computed. I'd recommend doing this in ETL, either in your source system if you have access/authority to initiate those changes, or as a part of your Power Query step before loading this data to the model. See a sample below for how you might implement this:

Here you go, a nice sample. You can manually enter a multiplier table ('Enter Data') and then copy your sales query and join in the multipliers for each year, then do your multiplication. The result is a FactGoal table that you can then just have some simple measures defined against. This is how we'll set up this sort of reporting for any of our clients.

View solution in original post

sushicat_san Advocate II
Advocate II

Re: Calculated Measure Based on Criteria - Aggregation Issue?

Thanks Greg. This is all new to me, so I appreciate the help!

jadhav_vilas84 Advocate II
Advocate II

Re: Calculated Measure Based on Criteria - Aggregation Issue?

Dear Sir, 

 

If you are using same measure with criteria like 

last Year product sales = calculate(SUM(PBI_MR_001[Product_Sale]), filter(PBI_MR_001, PBI_MR_001[Year_Id])=1)), Here 1 for last year. Current Year product sales = calculate(SUM(PBI_MR_001[Product_Sale]), filter(PBI_MR_001, PBI_MR_001[Year_Id])=2))

Year_IDYear 
12015
22016

, and product table is 

ProductProduct_saleYear_Id
TomatoRs 20001
BeatRs 50001
carrotRs 150001
TomatoRs 120002
BeatRs 500002
carrotRs 350002

 

here is measure is working fine , But when evere i will use slicer for year , measures gives only selected years value and another is blanck , Please suggest how can I solve this. 

 

thanks 

vilas jadhav

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors