Forum Discussion

AwaisAnwar's avatar
AwaisAnwar
Frequent Visitor
2 years ago

Zero values in summarize function

I have full year data but when i use sumraize function it iliminates zero values or no value. I want to have zero for customer who have no purchase in any week      
Need Help
Ahmedx's avatar
Ahmedx
Super User
2 years ago

pls try this

test = 
ADDCOLUMNS(
     SUMMARIZECOLUMNS(
        'Weekly Data',
       'Weekly Data'[RoundNumber],
       'Weekly Data'[LocationReference],
       'Weekly Data'[LocationName],
       'Weekly Data'[Customer Catagoryl_3],
        'Weekly Data'[W.E. Inv Date]),

"Total Sales",
IF(SUM('Weekly Data’[NettTotal])=0,0,SUM('Weekly Data'[NettTotal])))
  • AwaisAnwar's avatar
    AwaisAnwar
    Frequent Visitor
    2 years ago

    result is incorrect

    test = ADDCOLUMNS(
         SUMMARIZECOLUMNS(
           'Weekly Data'[RoundNumber],
           'Weekly Data'[LocationReference],
           'Weekly Data'[LocationName],
           'Weekly Data'[Customer Catagory1_3],
            'Weekly Data'[W.E. Inv Date]),

    "Total Sales",IF(SUM('Weekly Data'[NettTotal])=0,0,SUM('Weekly Data'[NettTotal])))

     

     

    • AwaisAnwar's avatar
      AwaisAnwar
      Frequent Visitor
      2 years ago

       

      now sale calculation is ok but facining original problem to get all weeks data

       

      test = ADDCOLUMNS(
           SUMMARIZECOLUMNS(
             'Weekly Data'[RoundNumber],
             'Weekly Data'[LocationReference],
             'Weekly Data'[LocationName],
             'Weekly Data'[Customer Catagory1_3],
              'Weekly Data'[W.E. Inv Date]),
      "Total Sales",IF(CALCULATE(SUM('Weekly Data'[NettTotal]),ALLEXCEPT('Weekly Data','Weekly Data'[LocationReference],'Weekly Data'[W.E. Inv Date]))=0,0,CALCULATE(SUM('Weekly Data'[NettTotal]),ALLEXCEPT('Weekly Data','Weekly Data'[LocationReference],'Weekly Data'[W.E. Inv Date]))))