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.

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.