Middle number Qty wise

[xuexi1890]

Hello,

I want to use a measure to extract a middle price at Qty wise, and respond to the filters (Year, sales area etc.)

below is a summary table:

Price           Sum of Resale Qty                 % of Accu. Resale Qty/Grand Total

0.50                  849,305                                           16.5%

0.56                  835,188                                           32.7%

0.59                  315,704                                           38.8%

0.60                  152,779                                           41.8%

0.61                  248,481                                           46.6%

0.70                  932,176                                           64.6%

0.77                  572,559                                           75.8%

0.80                  669,245                                           88.7%

0.81                  581,025                                           100.0%

0.7 is the price i want to have.

I am now using a stupid and not interactive way to extract this info:

1. Create a summary table as above, and use running total to calculate the percentage;
2. Export the table, use a measure to calcuate the min price when the accumulated % is >=50%

Please please help!

Thank you in advance!

Regards

Nate

[v-xicai · Accepted solution]

Hi @xuexi1890 ,

You can create measures like DAX below.

% of Accu. Resale Qty/Grand Total = CALCULATE(DIVIDE(SUM('Table'[Sum of Resale Qty]),CALCULATE(SUM('Table'[Sum of Resale Qty]),ALL('Table'))),FILTER(ALLSELECTED('Table'),'Table'[Price]<=MAX('Table'[Price])))
 
Middle number Qty = CALCULATE(MIN('Table'[Price]),FILTER(ALLSELECTED('Table'),'Table'[% of Accu. Resale Qty/Grand Total]>=0.5))

Best Regards,

Amy

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