Forum Discussion
Issue with count and Total
Having an issue with my Count and totals
Issue #1.
Trying to take the count of the Status with "Ready" of orders by date. The fact table contains the Order # and the date and status.
I am using Distinct count since the fact will have order and line details also.
| Order No | Total Price | Sum of Freight | Weight |
| 10003 | $77,500 | 45 | 10878 |
| 10005 | $110,375 | 45 | 10878 |
| Total | 187,875 | 45 | 10878 |
| Order No | Total Price | Sum of Freight | Weight |
| 10003 | $77,500 | 45 | 2250 |
| 10005 | $110,375 | 45 | 1300 |
| Total | 187,875 | 90 | 3550 |
Attaching my pbix.
https://drive.google.com/file/d/1FvG6KSw16V0rmq_N6Mf-DRHr1rNI0KRJ/view?usp=sharing
Anonymous See the attached pbix below my signature and explanation below for what I have done.
Issue 1
You are doing a distinctcount on status, and filtering for 1 status ="Ready", so the result will always be 1 or zero.
Try doing a DISTINCTCOUNT(Fact[OrderNo]) instead, or if you have a Order table you could COUNTROWS on that table, but in that case your filter might get more complex. So the simple solution is:
Status Count = Calculate(DISTINCTCOUNT('Fact'[Order No]),'Fact'[Status] = "Ready")Issue 2
You need relationships between Orders and Fact table. I have not related Orders Detail as it looks like your fact table has all that info so Orders Detail is redundant?
See if these three posts help with relating your data: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html
Once you have the tables related properly, you can use a measure such as:
Total Weight = SUMX(FILTER(Orders, CALCULATE(SUM('Fact'[Total Price])) > 0),Orders[Weight])to calculate Total Weight.Freight is simpler since you need a value for every row of Fact table:Total Freight = SUMX('Fact', RELATED('Shipper charge'[Freight]))
1 Reply
Anonymous See the attached pbix below my signature and explanation below for what I have done.
Issue 1
You are doing a distinctcount on status, and filtering for 1 status ="Ready", so the result will always be 1 or zero.
Try doing a DISTINCTCOUNT(Fact[OrderNo]) instead, or if you have a Order table you could COUNTROWS on that table, but in that case your filter might get more complex. So the simple solution is:
Status Count = Calculate(DISTINCTCOUNT('Fact'[Order No]),'Fact'[Status] = "Ready")Issue 2
You need relationships between Orders and Fact table. I have not related Orders Detail as it looks like your fact table has all that info so Orders Detail is redundant?
See if these three posts help with relating your data: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html
Once you have the tables related properly, you can use a measure such as:
Total Weight = SUMX(FILTER(Orders, CALCULATE(SUM('Fact'[Total Price])) > 0),Orders[Weight])to calculate Total Weight.Freight is simpler since you need a value for every row of Fact table:Total Freight = SUMX('Fact', RELATED('Shipper charge'[Freight]))