Issue with count and Total

[Anonymous]

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. 

Status Count = Calculate(DISTINCTCOUNT('Fact'[Status]),'Fact'[Status] = "Ready")

My expected result for 02/16 is a count of 2. But my measure is showing only 1. 

Issue #2.

The totals on the weight is off. The weight information for the order is only at the Orders header table. Fact includes both header & item level details. I am trying to bring the total into the visual, but they seem off when i do sum to get the total line. 

Order No	Total Price	Sum of Freight	Weight
10003	$77,500	45	10878
10005	$110,375	45	10878
Total	187,875	45	10878

 Expected result is 

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

[AllisonKennedy · Accepted solution]

@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]))