DAX Help / Design Help Only have part of Expression Filtered

• Steven.Grzybowski

  Hall of Fame

  Points: 3214

  More actions

  November 16, 2017 at 9:39 am

  #341381

  I am using Power BI as a front end for an SSAS Tabular Model.   Right now, I have a series of measures as follows: 

  AssetBalances = CALCULATE ((SUM ( Fact_Gld[endbal] )),FILTER ( VALUES ( DimCoa[COA_Type] ), DimCoa[COA_Type] = "Asset" ))

  Inventory Balances = CALCULATE(SUM(Fact_Gld[endbal]),FILTER ( VALUES ( DimCOA[GRP_GroupName] ), DimCOA[GRP_GroupName] = "Inventories" ))

  Inventory as % of Assets = [Inventory Balances]/ [AssetBalances]

  When looking at things from an aggregate level, this works fine.  However, When drilling down to the specific type of inventory (WIP, Finished Goods, etc.) The Inventory as % of Assets measure becomes useless because the result will always be 100% , since all of the inventory types are all inventory.   

  For the AssetBalances measure, I have a table called fact_GLD that contains the endbalance column that is being used, which is linked to  the following tables.   

  DimCOA - Chart of accounts.  The hierarchy of fields to filter on is as follows 
  COA_Type
  GRP_Groupname 
  SGRP_subgroupname
  COA_Description 
  AccountKey 

  The relationship to DimCOA is on the Accountkey Field

  DimDate -
  The datekey field on dimdate is related to the fact_gld table using the begindate field

  DimLocation -
  The Dimlocation table is joined to the fact_gld table using the field Locationkey

  What I would like to do is have a way to break the inventory accounts show Inventory as % of Assets Broken out by the type of inventory (COA_Description)  such that when loaded onto a matrix, the Inventory as % of Assets does not show 100%, but instead as what percentage of the assets are made up by each type of inventory.  

  The Matrix I am using has LocationName & COA Description (Which contains the type of inventory)  on the Rows, with a year, quarter, period date hierarchy on the columns. 

  I am open to redesigning the model itself / the sql database that feeds into this as well.  My thought process was not to create a new date dimension for inventory types because the dimension would only apply to a small subset of the accounts in the dimCOA dimension.