topic Re: Divide Count of Raw and quantities based on slicer in DAX Commands and Tips

Divide Count of Raw and quantities based on slicer

Mugeo77 — Fri, 14 Nov 2025 10:24:33 GMT

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?

Re: Divide Count of Raw and quantities based on slicer

Ahmed-Elfeel — Fri, 14 Nov 2025 21:50:12 GMT

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.

Re: Divide Count of Raw and quantities based on slicer

Praful_Potphode — Sat, 15 Nov 2025 02:32:45 GMT

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

Re: Divide Count of Raw and quantities based on slicer

Mugeo77 — Sat, 15 Nov 2025 23:15:47 GMT

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])

)

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

Re: Divide Count of Raw and quantities based on slicer

v-karpurapud — Thu, 20 Nov 2025 04:37:55 GMT

Hi Mugeo77

Thank you for the update. If you have any more questions, please let us know and we’ll be happy to help.

Regards,

Microsoft Fabric Community Support Team