Data modeling- multiple subcategories in a dimension

[eloomis]

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	U.S. Treasury	2
Government	Acency/Sovereigns	3
Credit	Corporate	4
Credit	Municipals	5
Securitized	RMBS	6

I then have a Fact table where I am doing the following DAX calculation. 

# Portfolio % = 
DIVIDE(
    [# Total_Market_Value_Base],
    CALCULATE(
        SUMX(FactPortfolioSectorDetails,[# Total_Market_Value_Base]),
        ALLSELECTED(FactPortfolioSectorDetails)
    )
)

# Total_Market_Value_Base = 
SUMX(FactPortfolioSectorDetails,[Market Value (Base)] + ([Accrued Interest] * [FX to Target]))

A scrubbed dataset with for FactPortfolioSectorDetails can be found here: https://docs.google.com/spreadsheets/d/1XIEuyCVwXE7pGR5YToK4S_dT9_prWkpQ/edit?usp=sharing&ouid=11467...

This is the visual.

• Port % is [# Portfolio %] measure.
• Sector is [Asset Type Subcategory] in FactPorfolioSectorDetails.
• The subcategories of Sector are [Asset Type] in the fact table.
• There is a slicer filtering the visual to display a single Account ID.

 

When Government is minimized, it displays the correct value. When I expand it, it just divides the value for government in half instead of recalculating for each subsector. I need Portfolio % to calculate independently for each subsector grouping, and then calculate for the overall sector grouping. I'm not sure what I have wrong in my model to fix this.

I tried splitting Sector and Subsector to different dimension tables and having a key relating to FactPortfolioSectorDetails but this did not work. I also know that I have Sector and Subsector as values in my fact table, but I need to have a sector dimension to use in the visual, as there is another fact table in the schema which also will be supplying fields to this visual and need to be related with the dimension.

Any help is much appreciated.

For reference, here is a screenshot of the model.

[eloomis · Accepted solution]

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!

[v-rzhou-msft]

Hi @eloomis ,

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.

[amitchandak]

@eloomis , 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