0

I am looking for help on creating a measure that is a % of itself or % of the column total. Currently I have to create a duplicate of the aggregate (customers) and then in format values --> Show as % of Column total. I would like to make the measure explicitly that %.

Example:

State | Customers | New Measure GA | 500 | 50% SC | 250 | 25% NY | 250 | 25%

I would want this measure to update based on whatever filter or row column i use in my PowerPivot table.

Brandon
  • 70
  • 9

3 Answers3

1

Use this expression:

%cust = FORMAT((Sum(Tabla[Customers])/1000),"0%")

It will create a measure called %cust formatted to percent without decimals. If you want to get the decimals just replace "0%" by "0.00%".

enter image description here

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • This didn't work exactly but thats because I didn't provide as much info as needed, but you did get me started and I found out this is what I was looking for. – Brandon Jul 29 '16 at 19:05
  • Cust%:=Sum(table[cust])/CALCULATE(sum(table[cust]),ALL(table)) – Brandon Jul 29 '16 at 19:06
0

This formula worked for me.

Cust%:=Sum(table[cust])/CALCULATE(sum(table[cust]),ALL(table))

Oh and I format the measure as a % since the value comes out as 1

Brandon
  • 70
  • 9
0

Define a sum measure -

[cust_msr]:
=sum(table[cust])

Define the % measure:

[Cust%:]=
divide(
[cust_msr]
,CALCULATE([cust_msr],ALL(table))
,blank())

Using divide is best practice as it means any error values are set to blank values. Of course, you can set them as 0 or -1 etc if you'd like.

Also, set the measure data type in the powerpivot window and you won't need to faff around with format()

dijksterhuis
  • 1,225
  • 11
  • 25