Excluding filter for the measured column in Matrix

[Anonymous]

Dear Community Folks,

      We have a certain problem in achieving the following result. Pls refer the below pbix file, 

Dataset for your ref:

PBIX file

Excel File

Definition:

Our dataset has following things for your understaning,

1. We have Fact table, Project Dimension table and Account Dimension table.

2. Filters: Account & Project filters

3. Matrix table should have Project No, Account, Amount, 1200. In which 1200 is a measured column. 

Problem:

We wanted to have values to be populated on Column named "1200". But, with the current selection of filters, we are getting only the blank values on the column 1200. Please note that, we filtered out the 1200 on the slicer as per our need. 

Solution Needed:
When the Slicer is chosen as 5001 and all projects from Project No slicer, we need to have a summed value for the account number 1200. 

Note: 
We are using the following formula for calculating column 1200,

1200 =
CALCULATE (
    SUM ( 'Fact'[Amount] ),
    FILTER ( 'Fact', 'Fact'[Account ] = "1200" )
)

Expected Output:

Thanks in advance,
Praveen. 

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Based on your expected output, I'm a little confused about your logic. why sum value of 1200 is 30 not 35?

If your result should be 30, it seems that you shoould have the condition 'Fact'[Project ID]=1.

If that is your scenario, you could try the formula below.

1200 =
CALCULATE (
    SUM ( 'Fact'[Amount] ),
    FILTER (
        ALLEXCEPT ( 'Fact', Project[Project No] ),
        'Fact'[Account ] = "1200"
            && 'Fact'[Project ID] = 1
    )
)

Here is your expected output.

Hope this can help you. 

Best Regards,

Cherry

[Anonymous]

Thank you very much Cherry for your timely help.
That's awesome. But, I have another issue now. Could you pls help? 

When I select 5001 in the slicer, there has to be Amount column populated with its summed values and also for the 1200 column with its concerned summed values. 
For example, If 5001 selected on filter, then display the Summed Amount on the 5001 column and Sum of 1200 values on the 1200 column only; not for other columns. 

Which means for the 5001 filter show only the 1200 values and rest of the columns should be shown as blank or null. 

IF 5001, then 1200 or ELSE Null or blank on other columns (1300, 1400, 1500)

IF 5002, then 1300 or ELSE Null or blank on other columns (1200, 1400, 1500)

IF 5003, then 1400 or ELSE Null or blank on other columns (1200, 1300, 1500)

IF 5004, then 1500 or ELSE Null or blank on other columns (1200, 1300, 1400)

Please take a look at the Updated sample file URLs,

Updated pbix

Updated Excel

Formulas Used:

1200 =
CALCULATE (
SUM ('Fact'[Amount]),
FILTER (ALLEXCEPT( 'Fact',Project), 'Fact'[Account ] = "1200"))

1300 = 
CALCULATE (
SUM ('Fact'[Amount]),
FILTER (ALLEXCEPT( 'Fact',Project), 'Fact'[Account ] = "1300"))

1400 = 
CALCULATE (
SUM ('Fact'[Amount]),
FILTER (ALLEXCEPT( 'Fact',Project), 'Fact'[Account ] = "1400"))

1500 = 
CALCULATE (
SUM ('Fact'[Amount]),
FILTER (ALLEXCEPT( 'Fact',Project), 'Fact'[Account ] = "1500"))

Actual Output what I'm getting now:

Expected Results

Note: The amount column should display the correct values for each Account numbers 5001, 5002, 5003, 5004. 

Many Thanks,

Praveen.