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!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

 

I think you can try this code to create a measure.

# Portfolio % =
IF (
    ISINSCOPE ( DimSector[SubSector] ),
    DIVIDE (
        [# Total_Market_Value_Base],
        CALCULATE (
            SUMX ( FactPortfolioSectorDetails, [# Total_Market_Value_Base] ),
            FILTER (
                ALLSELECTED ( FactPortfolioSectorDetails ),
                FactPortfolioSectorDetails[Asset Type Subcategory] = MAX ( DimSector[Sector] )
            )
        )
    ),
    DIVIDE (
        [# Total_Market_Value_Base],
        CALCULATE (
            SUMX ( FactPortfolioSectorDetails, [# Total_Market_Value_Base] ),
            ALLSELECTED ( FactPortfolioSectorDetails )
        )
    )
)

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.