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.
Object: to create a Incoming Total Sales Measure -
Incoming Total Sales = CALCULATE(SUM('SALES DETAILS'[SalesTxn Document Total Amount]), FILTER('SALES DETAILS', 'SALES DETAILS'[SalesTxn Document Type] = "Sales Order"))
When I filter the reference number on the dataset- the Measure is correct - (sorry for the first image - I realized I uploaded the wrong photo. Sorry if it caused any confusion! I'll upload the correct filtered image when I get back to that particular computer).
However, when I remove a filter - it doubles (or more) the value in the Measure for some reason:
But not on every row. Just on some. I can't find a particular pattern. Because of this - it skews the total amount as well, nearly doubling the value.
Any assistance would be greatly appreciated. I'm not sure why it's behaving this way.
Solved! Go to Solution.
I figured out what was wrong.... 🙂 Oddly enough the Document Total column from the database was WRONG - my dax measure was closer than I thought.
I did however, need to add the Tax field to get the exact amount for each Sales Order:
Correct Measure is here:
Dollars w/ Tax = SUM('SALES DETAILS'[SalesTxn Document Sales Tax Amount]) + SUM('SALES DETAILS'[SalesTxn Line Sales Order Original Amount])
And then to configure Incoming Sales:
Incoming Total Sales = CALCULATE([Dollars w/ Tax], 'SALES DETAILS'[SalesTxn Document Type] = "Sales Order")
So it took two steps to create an 'Incoming Total Sales' measure using QuickBooks data.
Thanks @v-caliao-msft for attempting to help me! And you were right on the duplicates - QuickBooks generates multiple rows for the same order depending on the tasks being applied.
Incoming Total Sales SalesTxn Document Total Amount Sales Document Reference Number
$396.00 396 20581
$352.00 352 20582
$360.00 384.3 20583
$228.00 228 20584
$50.00 50 20585
$1,960.00 1960 20586
$348.00 348 20587
$400.00 428 20588
$520.00 520 2 0589
$198.50 198.5 20590
$215.00 215 20591
$175.00 175 20592
$617.00 1234 20593
$375.00 1125 20594
$376.00 376 20595
$1,493.72 5974.88 20596
$200.25 400.5 20597
$1,498.60 5994.4 20598
$172.00 172 20599
$1,085.00 2170 20660
$975.00 1950 20661
$3,525.00 7050 20668
I figured out what was wrong.... 🙂 Oddly enough the Document Total column from the database was WRONG - my dax measure was closer than I thought.
I did however, need to add the Tax field to get the exact amount for each Sales Order:
Correct Measure is here:
Dollars w/ Tax = SUM('SALES DETAILS'[SalesTxn Document Sales Tax Amount]) + SUM('SALES DETAILS'[SalesTxn Line Sales Order Original Amount])
And then to configure Incoming Sales:
Incoming Total Sales = CALCULATE([Dollars w/ Tax], 'SALES DETAILS'[SalesTxn Document Type] = "Sales Order")
So it took two steps to create an 'Incoming Total Sales' measure using QuickBooks data.
Thanks @v-caliao-msft for attempting to help me! And you were right on the duplicates - QuickBooks generates multiple rows for the same order depending on the tasks being applied.
Could you please share some sample data? It seems that there are multiple records for the sameReference Number in your dataset. So that when you remove the filter, the measure value doubles.
Regards,
Charlie Liao
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |