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:

SectorSubSectorSector Key
Cash 1
GovernmentU.S. Treasury2
GovernmentAcency/Sovereigns3
CreditCorporate4
CreditMunicipals5
SecuritizedRMBS6

 

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=114676334568225647500&rtpof=true&sd=true

 

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.

 

 

 

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!

3 Replies

  • amitchandak's avatar
    amitchandak
    Super 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

  • 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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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!