Count rows after summarizing

[sadath]

Hi

My 2 tables look like below

table1 >> Store_Name Sales_Man Date Sales
table2 >> Store_Name Sales_Man Date Target


How can i get number of stores which sales more than target.
Sales_Man name in row filter and in value area should show how many stores (count) achieved the target, for the given period of date (date is selected from slicer).


eg. if date 1st Apr to 10th Apr is selected from slicer
then formula should calculate the total sales & total target for each store then calculate how many sales are more than target

Can anyone help

 

[Jerry Sullivan]

Hi sadyboy,

First make a lookup table of all unique Store_Name values (with no duplicates). Name the table "StoreLookup".

Add this lookup table to your data model, then make these two relationships:
StoreLookup[Store_Name] to Table1[Store_Name]
StoreLookup[Store_Name] to Table2[Store_Name]

Create an new empty PivotTable from the Data Model

From the Excel Worksheet with the PivotTable, add these three measures:

Table Name: Table2
Measure Name: TotalTargetSales
Formula: =SUM(Table2[Target])

Table Name: Table1
Measure Name: TotalSales
Formula: =SUM(Table1[Sales])

Table Name: Table1
Measure Name: CountStoresGreaterThanTarget
Formula: =SUMX(VALUES(StoreLookup[Store_Name]),IF([TotalSales]-[TotalTargetSales]>0,1,0))

Now add these fields to your PivotTable:
Filters: (Your date field)

Values:
fx TotalTargetSales
fx TotalSales
fx CountStoresGreaterThanTarget

The end result should look something like this...

Book1
	B	C	D	E
1
2	Date	(Multiple Items)
3
4	Row Labels	TotalTargetSales	TotalSales	CountStoresGreaterThanTarget
5	Store01	700	723	1
6	Store02	1,400	1,033	0
7	Store03	700	300	0
8	Store04	1,400	1,488	1
9	Store05	700	955	1
10	Grand Total	4,900	4,499	3
Pivot

 

[sadath]

Hi
Great!!, thank you so much...