Sum up the distinct counts with a filter

[Anonymous]

Hello, 

I have a table that has for each hour if any assets of ours were down.

I have the following calulation which serves my purposes : 

Hours Down = CALCULATE(DISTINCTCOUNT(Site_Availability_Historyz_Data[Date_Time_History]),Site_Availability_Historyz_Data[AssetPK]<>BLANK())

This gives me the all of the hours that were down at a given site regardless if there were 3 rows for a given hour even though 3 assets could have been down, and therefore 3 unique rows for 15:00 hours.

However, the total within the table is not summing the distinct counts. I know I need to use a sumx, but I have having trouble with the syntax. Can anyone help? Initally, I tried the following:

SUMX(Site_Availability_Historyz_Data,CALCULATE(DISTINCTCOUNT(Site_Availability_Historyz_Data[Date_Time_History]),Site_Availability_Historyz_Data[AssetPK]<>BLANK()))

however, certain rows become inaccurate because it is essentially doubling the count of hours when there are two different assets down at a given hour.  

Any advice?

[Anonymous]

Still having issues with summarize as its counting duplicates values of column A when column B is different. Would a group by work better?

[mahoneypat]

Not totally clear but the issue is probably the table in your SUMX.  Please try this pattern in your measure

NewMeasure = SUMX(VALUES(Table[Column]), CALCULATE(DISTINCTCOUNT(Site_Availability_Historyz_Data[Date_Time_History]),Site_Availability_Historyz_Data[AssetPK]<>BLANK()))

Replace Table[Column] with the one used in your table visual.  If you have more than one column in your visual, you will need to replace the VALUES( ) expression with a SUMMARIZE one.  For example, SUMMARIZE(Table, Table[Column1], Table[Column2]).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

This is my data and how the measure is turning out. 

You'll see that the distinct count will get me 744, which is what I want, but the sumx measure to sum is getting me 921. 

I'll attatch July's data here https://1drv.ms/u/s!Ag08lU4J4vcXslqhttwvvNGL2vEm?e=R4KRAu 

[mahoneypat · Accepted solution]

Thank you for providing sample data.  Not sure if this is what you are looking for, but here is a SUMX measure that returns 744 as the result.  Your sample data only has 1 site value.  Does your actual data have multiple Site values?  If so, this measure will calculate the # of down hours for each site and include all in the total.

Hours Down =
SUMX (
VALUES ( 'in'[Site] ),
CALCULATE ( DISTINCTCOUNT ( 'in'[Date_Time_History] ) )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thank you so much! Can you explain to me why you used sites after values instead of date_Time_history?

[Anonymous]

Advice is: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490