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.
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 :
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Still having issues with summarize as its counting duplicates values of column A when column B is different. Would a group by work better?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you so much! Can you explain to me why you used sites after values instead of date_Time_history?
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 |
---|---|
48 | |
26 | |
20 | |
14 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |