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.   fo...
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.

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.

  • mehmetalidumlu's avatar
    mehmetalidumlu
    Copper Contributor
    Mar 29, 2021
    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.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      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.

Resources