Quick Measure Help - Sum-If function in PowerBI

[Harrisonbeck]

Hello,

I'd like some help with a calculated (or a few?) field please. I have a simplified sample of the kind of data I'm looking at below. I have a list of branches, qty shipped on a date, and if the shipment was successful. What I'd like to do is basically Sum sucessful  (Y) shipment qty per branch / total shipment qty per branch (all Y + N shipments). 

In antoher vizualization, I'd like to do successful shipments (count of Y) per branch / count of shipments per branch (Y + N).

Branch	Ship QTY	Success Ship?	Date
404	645	Y	1/15/2020
404	620	Y	2/19/2020
458	23	N	8/15/2020
458	548	Y	9/12/2020
550	678	Y	11/4/2020
550	2	Y	3/10/2020
840	21	N	4/18/2020
840	900	N	7/10/2020

The obejective is to get a chart that will show which branches have the highest % successful shipments, and which branches have the lowest % successful shipments. Actual data set has 10k+ rows with 110 different branches. 

Is this two separate calcluated fields? I've tried multiple ways to solve this but can't figure this out. 

Thanks in advance,

Harris 

[v-stephen-msft · Accepted solution]

Hi @Harrisonbeck ,

You don't need to use quick measure, just create a measure directly.

Try this measure

Result =
CALCULATE (
    DIVIDE (
        CALCULATE ( SUM ( 'Table'[Ship QTY] ), 'Table'[Success Ship?] = "Y" ),
        SUM ( 'Table'[Ship QTY] )
    ),
    ALLEXCEPT ( 'Table', 'Table'[Branch] )
)

Best Regards,

Stephen Tao

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

[amitchandak]

@Harrisonbeck , You can use TOPN and Rank

In the case of TOPN, you can add both top and bottom rank in measure

Refer my video for TOPN -https://www.youtube.com/watch?v=QIVEFp-QiOk

or

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[Harrisonbeck]

Hello,

This wasn't really enough information. I understand how to rank and only show the top 10 values, but I need to calculate If Branch X, (Sum of Y) / (Sum of Y + N). Can you help to solve this? 

Maybe it needs to be another table?

Thanks for your help. 

[amitchandak]

@Harrisonbeck , when you have not selected Success Ship in visual 

Try measure  

sum(calculate(sum(Table[Ship QTY]), filter(Table, Table[Success Ship] ="Y")), sum(Table[Ship QTY]))

if Success Ship is in visual 

Try a measure = 
sum(sum(Table[Ship QTY]),calculate(sum(Table[Ship QTY]), removefilters(Table[Success Ship])))