Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Data Modeling with 2 Fact Tables

I have the following 2 Tables :- 

Account Details

Person Details

 

Now each account can have multiple persons associated with it and one person can manage multiple accounts

Let's take the below example :-

 

I also have a Top N filter table where I have values for each Top N%

The above table means that when I select Top 50%, I need to select the top 50% accounts by revenue , within the context of other filters like Area 

I am trying to do the following :-

1. When user selects a Top N% Value (Let's say Top 50%) , a visual should return the top N% accounts by revenue 

2. Upon the same action, another visual should return the list of persons who are associated with the account and their Target and Actual (Ideally 1 row per person) 

I am wondering what would be a good way to model the data to achieve this , since it looks like I am filtering dimension tables(AccountId) based on filtering on Fact Tables.

 

Appreciate any help and guidance

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    1.Create a calculated column to rank.

    Rank =
RANKX (
    'Account Details',
    CALCULATE (
        SUM ( 'Account Details'[Revenue] ),
        ALLEXCEPT ( 'Account Details', 'Account Details'[AccountID] )
    ),
    ,
    ASC,
    DENSE
)

     

    2.Create a measure and put it into Filters. Set show items when the value is 1.

    Measure =
VAR maxrank =
    CALCULATE (
        MAX ( 'Account Details'[Rank] ),
        ALL ( 'Account Details' ),
        ALL ( 'Person Details' )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Top N filter'[TOp N%] ),
        "ALL", 1,
        "Top1%",
            IF ( MIN ( 'Account Details'[Rank] ) < maxrank * 0.01, 1 ),
        "Top5%",
            IF ( MIN ( 'Account Details'[Rank] ) < maxrank * 0.05, 1 ),
        "Top50%",
            IF ( MIN ( 'Account Details'[Rank] ) < maxrank * 0.5, 1 )
    )

     

     

     

    3.When Top50% is selected, the result is this.

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

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

3 Replies