Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Data modeling- multiple subcategories in a dimension

I have a matrix visual which has under rows a Sector and then a Subcategory, both in the same dimension table. DimSector looks like this: Sector SubSector Sector Key Cash   1 Government...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I was able to fix the problem without modifying the measures - I made a mistake in merging the dimension keys with the fact tables. Thank you for the reponses!

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

Anonymous , Switch measure based on level

 

# Portfolio % =
var _level1 = DIVIDE(
[# Total_Market_Value_Base],
CALCULATE(
SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]),
removefilters(FactPortfolioSectorDetails[Subsecor])
)
)

var _level2 = DIVIDE(
[# Total_Market_Value_Base],
CALCULATE(
SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]),
ALLSELECTED(FactPortfolioSectorDetails)
)
)

return
if(isinscope(FactPortfolioSectorDetails[Subsecor]), _level1, _level2)

 

 

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA