Forum Discussion
Drill Level Matrix - Calculation Query
Is there a way to control the CALCULATION in Matrix based on Drill Level?
In my DATA table, i have column "Quantity". When using the MATRIX Visual, the Column "Quantity" & "Hours" is Summed up.
But in my case, i do not want the column QUANTITY summed up at "House" Level. This is because the data in the table is based of different Types / Unit of Measures [Cubic Meters, Linear Meters, Each], and hence summing up the quantity at "House" Level does not make sense. For example, we can say it took 500 hours to install 1000 cubic meters of Concrete and 1000 hours to build the house.
Hence at the "House" Level, "Quantity" should be BLANK. Is this possible?
Hi hackfifi,
Here is the calculated field formula i wrote for Quantity
=if(HASONEVALUE(Table3[Type]),SUM([Quantity]),BLANK())
Hope this helps.
6 Replies
Hi,
you can switch off the sub total option under format tab, but unfortunately you can’t control it by column wise.
Hi,
This should be doable, Please share the link from where i can download your Excel/PowerBI desktop file.
Thanks Ashish_Mathur
Hopefully the below link works
https://1drv.ms/x/s!Ahwb9FOvNbslhRbnLJ_0Zz8Gubxs
Hi hackfifi,
Here is the calculated field formula i wrote for Quantity
=if(HASONEVALUE(Table3[Type]),SUM([Quantity]),BLANK())
Hope this helps.
