Forum Discussion

VK7's avatar
VK7
Frequent Visitor
2 years ago
Solved

Multi usage measure

In a table, I have duplicate values, But I've to calcuate ignoring the duplicate.

In one chart need to dispaly the date (dd-mm-yy) wise and in another chart in Month mmm-yy.

1)In month, it is simple to have it without duplicate.
2) In Date, it should take sum without duplicate, and divide the value by 2.

 

 

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi VK7 


    Here I create a set of sample:

     

    In Power Query, both-select Name and Date column and right-click, select Remove Duplicates.

    Apply&Close

    Then add 2 calculated table:

     

    Table2  = SUMMARIZE('Table','Table'[Date],"_sum",SUM('Table'[Product Sold]))
    Table 3 = 
VAR _vtable = SELECTCOLUMNS('Table','Table'[Date],'Table'[Product Sold],"Year/Month",FORMAT('Table'[Date],"MMM-YY"))
RETURN 
SUMMARIZE(_vtable,[Year/Month],"_sum",SUMX(FILTER(_vtable,[Year/Month]=EARLIER([Year/Month])),[Product Sold]))

    The result is as follow:

     

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi VK7 

     

    Actually that i can't understand your expression well.

    The 2 Outputs seems too comfused, if possible, please share more detailed description of the outputs.

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • VK7's avatar
      VK7
      Frequent Visitor
      2 years ago

      I'm sorry for that, 

      Output1: We need a chart with Proper date wise (dd-mm-yyyy) by sum of sales but distinct value. So if you look at the image the value for 20-06-24 is 4, cos of one duplicate value which i highlightd in red.
      Output2: Same like output1 but isnstaed of proper date, need for only Monthly wise

      To add few more details, its too large volume & we cant revome duplicate.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi VK7 


        Here I create a set of sample:

         

        In Power Query, both-select Name and Date column and right-click, select Remove Duplicates.

        Apply&Close

        Then add 2 calculated table:

         

        Table2  = SUMMARIZE('Table','Table'[Date],"_sum",SUM('Table'[Product Sold]))
        Table 3 = 
VAR _vtable = SELECTCOLUMNS('Table','Table'[Date],'Table'[Product Sold],"Year/Month",FORMAT('Table'[Date],"MMM-YY"))
RETURN 
SUMMARIZE(_vtable,[Year/Month],"_sum",SUMX(FILTER(_vtable,[Year/Month]=EARLIER([Year/Month])),[Product Sold]))

        The result is as follow:

         

         

         

        Best Regards

        Zhengdong Xu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.