Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |