Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
Solved! Go to Solution.
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.
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_ID | Year |
1 | 2015 |
2 | 2016 |
, and product table is
Product | Product_sale | Year_Id |
Tomato | Rs 2000 | 1 |
Beat | Rs 5000 | 1 |
carrot | Rs 15000 | 1 |
Tomato | Rs 12000 | 2 |
Beat | Rs 50000 | 2 |
carrot | Rs 35000 | 2 |
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
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.
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!
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.
Thanks Greg. This is all new to me, so I appreciate the help!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |