Issue with matrix subtotals (all rows show the same number)

[Anonymous]

Hello,

I'm new to Power BI and I am still getting used to it, I've been trying to build a matrix for a dashboard and I am facing some issues witht the sub-totals.

Essentially I have "regions" and I can drop down to have exact locations and an inventory count in each.

The issue is that  when I close the drop down, the sub-total for a given region is the total of all inventory, at least I think it is because all regions seem to have the same sub-total which I know is not possible.

I was hoping someone could tell me how to have a given row's sub-total be the sum of the values when I expand that same row.

(I've attached a screenshot for reference)

Thank you in advance!

[Anonymous]

@Anonymous , is Sum of Current Month a measure? If so, please post the DAX formula of the measure.

Also, this issue sounds like it is due to the relationships between your tables. Could you post a screenshot of your data model?

[Anonymous]

I don't think sum of current month is a measure, I simpli slid it into values and chose sum it was count at first but that did not give the right results, I've attached the relationships below

I've used Region from the raw data dataset and Current location description as well as current month from the other. 

[Anonymous]

@Anonymous , thanks. What columns is the Active relationship between RawData and MeterReadingEquipment based on?

[Anonymous]

It is based on current location description

[Anonymous · Accepted solution]

@Anonymous , thanks.

What values are in the Current Month column, and what do those values mean? 

Instead of counting the rows in the dimension (MeterreadingEquipment) I think you should be counting rows in the fact table (Raw Data). Try this measure and put it on your matrix:

InventoryCount = 
    COUNTROWS('Raw Data')

[Anonymous]

It's a bit messy, the current month values are on another excel tab from the raw data and they represent the quantity of inventory at the month of the data collection. I will incorporate those values in the raw data tab and will try it thank you!

[Anonymous]

@Anonymous , you've probably already worked it out, but once you've moved the Current Month column into the Raw Data fact table, the measure should look something like this:

CurrentMonthInventory = 
    SUM('Raw Data'[Current Month Inventory])

 Or you could probably simply drag the new fact table column onto the matrix and the implicit measure would work.