Forum Discussion
Urgent Help! Populating a calculated column on Matrix based on Slicer selection
Dear All,
Please find the below dataset and pbix file for your ref.
Dataset for your ref:
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.
Formula Used:
We are using the following formula for calculating column 1200,
1200 =
CALCULATE (
SUM ( 'Fact'[Amount] ),
FILTER ( 'Fact', 'Fact'[Account ] = "1200" )
)
Actual Output What we are getting now:
Expected Output:
Addtional info:
When we select the 1200 on the Filter as shown in below screenshot, the values for the 1200 column is coming correctly. Ofcourse it would come. But, we want the values for Amount column and 1200 column separately.
Many Thanks,
Praveen.
Anonymous
Try with following
1200 = CALCULATE ( SUM ('Fact'[Amount]), FILTER (ALLEXCEPT( 'Fact',Project), 'Fact'[Account ] = "1200"))
2 Replies
Anonymous
Try with following
1200 = CALCULATE ( SUM ('Fact'[Amount]), FILTER (ALLEXCEPT( 'Fact',Project), 'Fact'[Account ] = "1200"))Thank you very much Zubair 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,
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.