Drill Level Matrix - Calculation Query

[hackfifi]

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?

[Ashish_Mathur]

Hi,

This should be doable,  Please share the link from where i can download your Excel/PowerBI desktop file.

[hackfifi]

Thanks @Ashish_Mathur

Hopefully the below link works

https://1drv.ms/x/s!Ahwb9FOvNbslhRbnLJ_0Zz8Gubxs

[v-ljerr-msft]

Hi @hackfifi,

Have you tried the solution provided by @Ashish_Mathur above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[Ashish_Mathur · Accepted solution]

Hi @hackfifi,

Here is the calculated field formula i wrote for Quantity

=if(HASONEVALUE(Table3[Type]),SUM([Quantity]),BLANK())

Hope this helps.

[dilumd]

Hi,

you can switch off the sub total option under format tab, but unfortunately you can’t control it by column wise.

[hackfifi]

Thanks @dilumd -Maybe i need to think of another visual to represent the data. Cheers