Creating a measure

[Marsh77]

Hi all,

I am new here so apologies if this is in the wrong place.

My raw data is in this sort of format in a data warehouse so i write a query within powerpivot and get a large table (over 900k rows)

I then pivot the data add slicers etc. in the columns on the powerpivot pivot table I have a column for existing and for new. I want to add a measure that does a ratio of the two. Up until now i have to cheat and just use the excel cells to the right of the table and format so it looks like it is part of the table. However now i have been asked to rank by product so i could do with some help. Does this make sense?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Region[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]01/01/2013[/TD]
[TD]UK[/TD]
[TD]Existing[/TD]
[TD]A[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]02/01/2013[/TD]
[TD]UK[/TD]
[TD]New[/TD]
[TD]A[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]03/01/2013[/TD]
[TD]UK[/TD]
[TD]Existing[/TD]
[TD]A[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]04/01/2013[/TD]
[TD]UK[/TD]
[TD]New[/TD]
[TD]A[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marsh77]

The above is a simplistic example. I am struggling as its not one columns of my raw data divided by a different column of my raw data. I cant see anything that lets you do it if its a result within a column spread over a number of colmuns within the pivot table

 

[cnort21]

Assuming Table1 is where you are storing in this data. I included the IFERROR statement to ignore the 5/0 ratio.


=IFERROR(COUNTROWS(FILTER(Table1,Table1[Customer]="New"))/COUNTROWS(FILTER(Table1,Table1[Customer]="Existing")),blank())

 

[theBardd]

What are you ranking, the number of customers, the value of New or Existing, or the ratio?

 

[Marsh77]

Hi its the ratio. Apologies for the delay in getting back to this thread