Forum Discussion

mehmetalidumlu's avatar
mehmetalidumlu
Copper Contributor
Mar 29, 2021
Solved

using data in power pivot

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.

 

customerdatesales via websales via employee
100044163.03.2021 0,20
1000441610.03.2021 0,10
1000441619.03.2021 0,70
100044274.03.2021 0,12
1000442725.03.20210,10 
100044491.03.2021 0,22
100044498.03.2021 0,10
1000444911.03.2021 0,12
1000444918.03.2021 0,12
1000444925.03.2021 0,10
100044622.03.2021 0,22
100044629.03.2021 0,22
1000446212.03.2021 0,22
1000446216.03.20210,24 
1000446223.03.2021 0,10
1000446226.03.2021 0,24
100045371.03.2021 0,66
100045374.03.2021 0,76
1000453711.03.2021 1,18
1000453718.03.2021 1,08
1000453720.03.2021 1,14
1000453723.03.20210,02 
1000453725.03.2021 0,76
1000453727.03.2021 0,98
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 31, 2021

    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 Replies

    • mehmetalidumlu's avatar
      mehmetalidumlu
      Copper Contributor
      Mar 29, 2021

      SergeiBaklan 

       

      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,

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Mar 29, 2021

        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.

Resources