Forum Discussion

Majidbhatti's avatar
Majidbhatti
Icon for Advocate IV rankAdvocate IV
6 years ago
Solved

Multiple table relationships

Hi,   I have 3 tables and relations as shown below. When i do the visuals by adding all 3 tables on the page;  Upon selecting COMPANY, DEPT and TRANS are filtered but upon selecting DEPT, it does ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Majidbhatti 

    First, create the relationship as below:

    Since the total for all [Amount] in the [trans] table is 302, so the measure grand total should be 302,

    Create measures

    Measure = SUM(trans[Amount])

Measure 2 =
IF (
    ISINSCOPE ( company[name] ),
    IF ( ISINSCOPE ( dept[desc] ), [Measure], SUMX ( dept, [Measure] ) ),
    SUMX ( company, [Measure] )
)

    If you want the grand total to be the sum of all the subtotals, you could try the measure below

    Measure 3 =
VAR c =
    CALCULATE ( DISTINCTCOUNT ( dept[desc] ), ALLSELECTED ( dept ) )
RETURN
    IF (
        ISINSCOPE ( company[name] ),
        IF ( ISINSCOPE ( dept[desc] ), [Measure], SUMX ( dept, [Measure] ) ),
        c * SUMX ( company, [Measure] )
    )

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Majidbhatti's avatar
Majidbhatti
Icon for Advocate IV rankAdvocate IV
6 years ago

v-juanli-msft I have changed the data in the sample file under your working. the data iin your file was not in the way it should be so i have modified the file explaining my problem. the total are all wrong and filtering is not working.

 

You can download the .pbix file here

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Majidbhatti 

First, create the relationship as below:

Since the total for all [Amount] in the [trans] table is 302, so the measure grand total should be 302,

Create measures

Measure = SUM(trans[Amount])

Measure 2 =
IF (
    ISINSCOPE ( company[name] ),
    IF ( ISINSCOPE ( dept[desc] ), [Measure], SUMX ( dept, [Measure] ) ),
    SUMX ( company, [Measure] )
)

If you want the grand total to be the sum of all the subtotals, you could try the measure below

Measure 3 =
VAR c =
    CALCULATE ( DISTINCTCOUNT ( dept[desc] ), ALLSELECTED ( dept ) )
RETURN
    IF (
        ISINSCOPE ( company[name] ),
        IF ( ISINSCOPE ( dept[desc] ), [Measure], SUMX ( dept, [Measure] ) ),
        c * SUMX ( company, [Measure] )
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.