Percentage measure of total per month

[cajste]

Hi,

I need a measure that calculates the sales per customer as a percentage of the total sales per month. I know this this is possible by Value field settings and select % of column, but then I can't use this measure in another measure.

I tried =SUM(Sales[Sales])/CALCULATE(SUM(Sales[Sales]);ALLSELECTED()), but that returns sales as a percentage of the total for all 4 months as shown below.[TABLE="width: 421"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]2012
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012 Total
[/TD]
[/TR]
[TR]
[TD]Customer
[/TD]
[TD]May
[/TD]
[TD]June
[/TD]
[TD]July
[/TD]
[TD]August
[/TD]
[/TR]
[TR]
[TD]Customer 1
[/TD]
[TD="align: right"]9 %
[/TD]
[TD="align: right"]9 %
[/TD]
[TD="align: right"]9 %
[/TD]
[TD="align: right"]9 %
[/TD]
[TD="align: right"]35 %
[/TD]
[/TR]
[TR]
[TD]Customer 2
[/TD]
[TD="align: right"]1 %
[/TD]
[TD="align: right"]0 %
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1 %
[/TD]
[/TR]
[TR]
[TD]Customer 3
[/TD]
[TD="align: right"]14 %
[/TD]
[TD="align: right"]13 %
[/TD]
[TD="align: right"]14 %
[/TD]
[TD="align: right"]14 %
[/TD]
[TD="align: right"]55 %
[/TD]
[/TR]
[TR]
[TD]Customer 4
[/TD]
[TD="align: right"]3 %
[/TD]
[TD="align: right"]2 %
[/TD]
[TD="align: right"]2 %
[/TD]
[TD="align: right"]3 %
[/TD]
[TD="align: right"]10 %
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD="align: right"]26 %
[/TD]
[TD="align: right"]24 %
[/TD]
[TD="align: right"]25 %
[/TD]
[TD="align: right"]25 %
[/TD]
[TD="align: right"]100 %
[/TD]
[/TR]
</tbody>[/TABLE]


Any suggestions on how to solve this?

Brgds,
Caj

 

[Priit]

Hi Caj

Use Pivottable instead of formula and use Show values as column total.

Regards,
Priit

Oops,

didnt notice that it was a powerpivot question
So my answer might not be relevant, but I feel that the ordinary pivot solved the problem.

PS Tried to install PowerPivot, checked the Com Addin box and the Powerpivot does not appear to the menu.

Priit

 

[Jacob Barnett]

Caj, its good practice to create intermediate measures to use in further measures. On this basis I would suggest creating 2 measures:

[SalesMeasure] = SUM(Sales[Sales])

[Customer Sales as %] = [SalesMeasure] / CALCULATE([Sales Measure], ALL(Sales[Customer))

This assumes you are using a simple single table structure. If you had a dedicated customer table then you would change the ALL() to ALL(Customer).

The ALL() function is arguably the most important in DAX (along with CALCULATE()) - it basically opens out the current context based on the table or columns you prescribe.

 

[Laurent C]

ALLSELECTED() allows you to retrieve the grand total for the current selections.

To calculate SUM(Sales[Sales]) for the current month, you need to remove all filters on your date table, and tell PowerPivot to use the current month to calculate the measure.

Assuming you have a date table, the expression to calculate the denominator would look like this:
[Sales - Current Month] := CALCULATE( SUM(Sales[Sales]) , ALL(Dates) , VALUES(Dates[Month]) )

 

[cajste]

Thanks for the input!

Both your solutions seems to be working. So did my formula above after I changed it a bit: =SUM(Sales[Sales])/CALCULATE(SUM(Sales[Sales]);ALLSELECTED(dim_Customer[Customer])

Good point with the intermediate measure, makes a lot of sense when a formula is used in other formulas.

//Caj

 

[Laurent C]

Good news! Note, of course, that both measures are not equivalent. Check which one suits your requirements when you put something else on row than customer information.