DAX Calculation To Bucket Aggregate Variances

[Hopeless Cub Fan]

I am trying to create aggregate positive and negative variance buckets based on my Power Pivot dataset. The basic steps I am trying to achieve are below:

• Step 1: Calculate a variance at an aggregate level, in this example “Group”
• Step 2: Look at the variance associated with each group and place it into either a “Positive” or “Negative” variance bucket
• Step 3: Be able to have all the associated variance roll up under either the “Positive” or “Negative” bucket

The table below provides a very basic outline of what I want.

I am sure this isn't very difficult, but I've already spend 6+ hours trying to get it on my own. Thanks in advance for any help you can offer!

Excel 2010

	A	B	C	D	E	F	G	H	I	J
A	Negative	Positive	E	Positive
B	Positive	Positive	B	Negative
C	Positive	Positive	I	Total
D	Negative	Positive	C
E	Positive	Positive	F
F	Positive	Positive	G
G	Positive	Negative	D
H	Negative	Negative	A
I	Positive	Negative	H
Total
* Groups above are aggregations of detailed data

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Step 1: Base Data[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Step 2: Sorted[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Step 3: Desired Result[/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Group[/TD]
[TD="align: center"]Variance[/TD]
[TD="align: center"]Variance Grouping[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Variance Grouping[/TD]
[TD="align: center"]Group[/TD]
[TD="align: center"]Variance[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Variance Grouping[/TD]
[TD="align: center"]Variance[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]-10[/TD]

[TD="align: right"][/TD]

[TD="align: right"]42[/TD]
[TD="align: right"][/TD]

[TD="align: right"]81[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]25[/TD]

[TD="align: right"][/TD]

[TD="align: right"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"]-23[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]58[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]-3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]42[/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"][/TD]

[TD="align: right"]-3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]-10[/TD]

[TD="align: right"][/TD]

[TD="align: right"]-10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"]-10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1
​

 

[theBardd]

What columns are used to calculate the variance?

 

[Hopeless Cub Fan]

YTD Sales Var = 2015 YTD Sales - 2014 YTD Sales

I use a slicer to pull the relevant YTD months into the pivot.

2014 YTD Sales: =CALCULATE(SUM(Table1[SALES]),Table1[YEAR]=2014)
2015 YTD Sales: =CALCULATE(SUM(Table1[SALES]),Table1[YEAR]=2015)
YTD Sales Var: =Table1[2015 YTD Sales]-Table1[2014 YTD Sales]

Below is a rank measure I created that is properly ranking the YTD year over year variances.

Rank By Product Group:
=if(
CALCULATE(SUM(Table1[SALES]),Table1[YEAR]=2015,FILTER(Table1,Table1[PERIOD]=3))=0,
0,
calculate(RANKX(FILTER(All(Table1[PRDGRPDESC]),Table1[YTD Sales Var]),Table1[YTD Sales Var],,,Dense),Table1[YEAR]=2015))

 

[scottsen]

You so crazy

I would really love for you to have a real calendar table, and not write measures specifically to a year (eg 2014). If you had a calendar/date table you could just use Sales YTD := CALCULATE(SUM(Table1[Sales]), DATESYTD(Dates[DateKey])) and such (that would work for any year...)

I don't have a SUPER strong feeling for writing a "Positive" and "Negative" measure here... vs a calculated column on the group table. The latter is certainly easier AND let's you slice the groups to just show 1 or the other. But using a measure makes it more dynamic. So, depends on your goals I guess.

Any thoughts on which way you are heading?

 

[Hopeless Cub Fan]

I am indeed crazy.

Ridiculous question, but what would a calendar table entail? My detailed data contains a month and year time dimension. Still kinda new to Power Pivot, so definitely interested in best practices. I haven't used any DAX time functions yet.

For the positive/negative piece, I would be interested in how I could do the groupings both using a measure and a calculated column. I'm guessing creating a calculated column is probably the preferred solution for the desired output below, which has evolved slightly to segment both the negative and positive variances via "Top X" and "All Other" buckets:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2014 YTD Sales[/TD]
[TD]2015 YTD Sales[/TD]
[TD]YTD Sales Var[/TD]
[TD]2014 YTD Qty[/TD]
[TD]2015 YTD Qty[/TD]
[TD]YTD Qty Var[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Top 10 Positive Variances[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]All Other Positive Variances[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Top 10 Negative Variances
All Other Negative Variances

In this situation above variances are ranked based on the "YTD Sales Var" field, largest positive variance = top. Thanks for the help!

 

[Hopeless Cub Fan]

Mind. Blown.

Power Pivot Date Table | Power Pivot | Tiny Lizard

 

[theBardd]

Your month and year table is the basis of such a date table, but it should contain EVERY date between your first possible and last envisaged date. You will have columns for Calendar year (of that date), Month Number, Month Name, maybe Calendar Quarter, maybe Fiscal Year and Quarter, and any other columns that you might feel useful. That table would be loaded into Power Pivot and marked as a date table (there is a ribbon function to do this). Link the date/calendar table to your fact table by the date. The world of time intelligence (such as the measure Scott mentioned) is available to you.

There is one on Azure that you can just plug into The Ultimate Date Table - PowerPivotPro PowerPivotPro, or you could create it in Excel or Power Query, or write some SQL to create one.