Forum Discussion
Quick Measure Help - Sum-If function in PowerBI
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
Hi Anonymous ,
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.
4 Replies
Anonymous , 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-measuresAnonymous , 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])))
Hi Anonymous ,
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.
