Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi every one
I have requirement to display data in Matrix and update "Opening Balance" calculated Column based on following rules
The above is working, but issue is Total of opening balance measure is exceeding Assetts Acquisition price; (summed repetitively on each row)
How can I display the Opening balance so that it
Do i have to use a different format? 🙂
Please click here for the PBIX File
will appreciate if you can help
Solved! Go to Solution.
hi, @Anonymous
There is a wrong logic for your Total of opening balance measure.
First, there are four rows of data for FIXEDASSETNUMBER"11000", so there four rows of data for ACQUISITIONPRICE "13342.77"
Therefore, SUM ( 'Query1'[ACQUISITIONPRICE] ) will be calculated repetitively for FIXEDASSETNUMBER which has multiple rows of data.
Second, for the relationship between date table and Query1, Cardinality should be Many to One, and Cross filter direction should be "Single"
Then you could try this data model:
Step1:
Create a FIXEDASSETNUMBER and ACQUISITIONPRICE fact table then create the relationship with Query1 by FIXEDASSETNUMBER
Note: Be careful with the relationship between each table.
Step2:
Then adjust your formula
Measure 3 = VAR a = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR b = //MAX ( 'Query1'[ACQUISITIONDATE] ) MAX ( 'Query1'[TRANSDATE] ) RETURN IF ( a = b, SUM ( 'Fact'[ACQUISITIONPRICE] ), IF ( a > b, SUM ( 'Fact'[ACQUISITIONPRICE]) + SUM ( 'Query1'[Acquisition_Add] ) - SUM ( 'Query1'[Depreciation_Add] ) - SUM ( 'Query1'[Writedown_Add] ) ) )
Step3:
Drag FIXEDASSETNUMBER and ACQUISITIONPRICE from fact table instead of Query1
Result:
here is new pbix file, please try it.
Best Regards,
Lin
hi, @Anonymous
There is a wrong logic for your Total of opening balance measure.
First, there are four rows of data for FIXEDASSETNUMBER"11000", so there four rows of data for ACQUISITIONPRICE "13342.77"
Therefore, SUM ( 'Query1'[ACQUISITIONPRICE] ) will be calculated repetitively for FIXEDASSETNUMBER which has multiple rows of data.
Second, for the relationship between date table and Query1, Cardinality should be Many to One, and Cross filter direction should be "Single"
Then you could try this data model:
Step1:
Create a FIXEDASSETNUMBER and ACQUISITIONPRICE fact table then create the relationship with Query1 by FIXEDASSETNUMBER
Note: Be careful with the relationship between each table.
Step2:
Then adjust your formula
Measure 3 = VAR a = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR b = //MAX ( 'Query1'[ACQUISITIONDATE] ) MAX ( 'Query1'[TRANSDATE] ) RETURN IF ( a = b, SUM ( 'Fact'[ACQUISITIONPRICE] ), IF ( a > b, SUM ( 'Fact'[ACQUISITIONPRICE]) + SUM ( 'Query1'[Acquisition_Add] ) - SUM ( 'Query1'[Depreciation_Add] ) - SUM ( 'Query1'[Writedown_Add] ) ) )
Step3:
Drag FIXEDASSETNUMBER and ACQUISITIONPRICE from fact table instead of Query1
Result:
here is new pbix file, please try it.
Best Regards,
Lin
Thank you a bundle Lin 🙂