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

3 Replies

  • 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]))))