Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |