Divide Count of Raw and quantities based on slicer

Mugeo77

Hi,

Looking for an help with a formula that help me to have a calculated column based on slicer applied

I have a table like this one

Branch	Type	Order	Qty	Item
123	BAC	10745632	4	1649390
123	BAC	10960419	2	1285893
123	BAC	10960419	4	1649390
456	BAC	11217854	4	1285893
123	BAC	11760278	4	1285893
123	BAC	11798972	5	1285893
123	BAC	11798972	4	1649435
456	BAC	12101628	4	1285893
123	BAC	12126970	4	1285893
123	BAC	12126970	2	1649435

I have a Measure that give me a distinct count of raws "Order" which is: 7.

If I have a slicer for column "Branch" as "123" the measure is 5

If I have a slicer for column "Branch" as "456" the measure is 2

I am looking for a formula called "ROC" that divide the Qty based on the count of raws in column "Order" as per the slicer applied.

If I want to see "ROC" based on all data the out put should be as per below where ROC = Qty / Count of raws column Order (7 in this case)

Branch	Type	Order	Qty	Item	ROC
123	BAC	10745632	4	1649390	0.571429
123	BAC	10960419	2	1285893	0.285714
123	BAC	10960419	4	1649390	0.571429
456	BAC	11217854	4	1285893	0.571429
123	BAC	11760278	4	1285893	0.571429
123	BAC	11798972	5	1285893	0.714286
123	BAC	11798972	4	1649435	0.571429
456	BAC	12101628	4	1285893	0.571429
123	BAC	12126970	4	1285893	0.571429
123	BAC	12126970	2	1649435	0.285714

If I want to see the "ROC" for "123" Branch the out put should be as per below where ROC = Qty / Count of raws column Order (5 in this case)

Branch	Type	Order	Qty	Item	ROC
123	BAC	10745632	4	1649390	0.8
123	BAC	10960419	2	1285893	0.4
123	BAC	10960419	4	1649390	0.8
123	BAC	11760278	4	1285893	0.8
123	BAC	11798972	5	1285893	1
123	BAC	11798972	4	1649435	0.8
123	BAC	12126970	4	1285893	0.8
123	BAC	12126970	2	1649435	0.4

If I want to see the "ROC" for "456" Branch the output should be as per below where ROC = Qty / Count of raws column Order (2 in this case)

Branch	Type	Order	Qty	Item	ROC
456	BAC	11217854	4	1285893	2
456	BAC	12101628	4	1285893	2

Anybody can help me?

Mugeo77

Thanks for your reply. I have combined both and come up with the solution that is working for me:

ROC Version 1 = DIVIDE(

[Sum QTY]

,CALCULATE([Order])

,0

)

Where I have changed the measure for the distinct order like this

Order = CALCULATE

(DISTINCTCOUNT('Table'[Order]),

ALLEXCEPT('Table','Table'[Branch],'Table'[Type])

This formula is working for me

Praful_Potphode

Hi @Mugeo77

Please try below:

ROC Version 1 = DIVIDE(
    [Total Quantity]
    ,CALCULATE([#Orders],ALLEXCEPT('Table','Table'[Branch]))
    ,0
    
    )

you can download PBIX from below link.

Sample PBIX

Please give Kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Ahmed-Elfeel

Hi @Mugeo77,

I hope you are doing well today ☺️❤️

So basically to create a dynamic ROC calculation that responds to slicer selections you will need to create a measure (not a calculated column) in Power BI.

Note: Calculated columns are static and will not respond to slicers whereas measures dynamically adjust based on filters

So try this basic DAX measure :

ROC = 
DIVIDE(
    SUM('YourTable'[Qty]),
    DISTINCTCOUNT('YourTable'[Order]),
    0
)

This measure works dynamically with any slicer/filter applied to your report
If you need to use this value in further calculations make sure you reference it as a measure (not a column)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.