using data in power pivot

[mehmetalidumlu]

hello, i am very new to power pivot and eager to learn abilities. i have a data like the one below. what i want to do is to find the sum of total sales of the customers who have sales via web.

for example 10004427 customers has sales via web and it's total sales is 0.22 and so on. the correct result for the example below is 8.04. how can i do it with powerpivot ?

note : my data has more columns, such as sales employee name, region etc. i want to be able to report in all those details.

customer	date	sales via web	sales via employee
10004416	3.03.2021		0,20
10004416	10.03.2021		0,10
10004416	19.03.2021		0,70
10004427	4.03.2021		0,12
10004427	25.03.2021	0,10
10004449	1.03.2021		0,22
10004449	8.03.2021		0,10
10004449	11.03.2021		0,12
10004449	18.03.2021		0,12
10004449	25.03.2021		0,10
10004462	2.03.2021		0,22
10004462	9.03.2021		0,22
10004462	12.03.2021		0,22
10004462	16.03.2021	0,24
10004462	23.03.2021		0,10
10004462	26.03.2021		0,24
10004537	1.03.2021		0,66
10004537	4.03.2021		0,76
10004537	11.03.2021		1,18
10004537	18.03.2021		1,08
10004537	20.03.2021		1,14
10004537	23.03.2021	0,02
10004537	25.03.2021		0,76
10004537	27.03.2021		0,98

[#2]

@mehmetalidumlu 

I guess PivotTable shall work here

Excel Pivot Tables | Exceljet

Create a PivotTable to analyze worksheet data - Office Support (microsoft.com)

Better to add data to data model creating it if more complex measures will be required.

[#3]

@Sergei Baklan 

Hi Sergei, thank you for the input.

this information is part of a bigger data set. and i can achieve the results I want, with multiple pivot tables and formulas.

having seen the capabilities of power pivot i thought i can do the whole thing with one single pivot table, and made much of it. but this part, I couldn't figure.

regards,

[#4]

@mehmetalidumlu 

Yes, Power Pivot provides more possibilities. In particular you may add your own measures in addition to ones which are built in into PivotTable, you may create and use relationships between tables, etc.

But you question was how to sum by customers, for that ordinary PivotTable is enough. Instead if using implicit measure you may add data to data table and use DAX measure like =SUM(Table1[sales via web]), result will be the same.

[#5]

I think, I havent expressed what i needed clearly.

In the sample data, customers 10004427, 10004462 and 10004537 have sales via web. customers 10004416 and 10004419 dont have sales via web. what i want to find is the total sales (sales via web + sales via employee) for customers 10004427, 10004462, 10004537, regardless of date. so in my sample data, for customer 10004427 total sales is 0.12+0.1 = 0.22, for customer 10004462 total sales is 0.22+0.22+0.22+0.24+0.1+0.24=1.24 and for customer 10004537 total sales is 0.66+0.76+1.18+1.08+1.14+0,02+0.76+0.98=6.58. for this sample data the grand total i want to see is 0.22+1.24+6.58=8.04. this can either be a new column or a measure, this is not important. maybe this is something that cant be done with power pivot.

thank you very much for your time and your help.

[#6 · Accepted solution]

@mehmetalidumlu 

Sorry, busy on this week and thank you for the clarification.

To receive result like this

we may use two measures

Sales:=VAR SW =
           SUMX ( Range, Range[sales via web] )
       VAR SE =
           SUMX ( Range, Range[sales via employee] )
       RETURN
           IF ( ISBLANK ( SW ), BLANK (), SW + SE )

and

Total Sales:=SUMX (
    GROUPBY (
        Range,
        Range[customer]
     ),
     [Sales]
)

Please check in attached file.

[#7]

@Sergei Baklan 

thank you very much for help, best regards.

[#8]

@mehmetalidumlu , you are welcome