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

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.