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 friends, how are you?
I need to count the balls different of green, someone help me to create this measure?
this measure is the average of a values in a period of time.
thanks a lot!
Solved! Go to Solution.
Hi, @vinicius_ramos
You can try the following methods.
Table:
Average =
SUMMARIZE (
'Table',
'Table'[Item],
"Average",
CALCULATE (
AVERAGE ( 'Table'[%] ),
FILTER ( ALL ( 'Table' ), [Item] = EARLIER ( 'Table'[Item] ) )
)
)
Column:
Ball collour =
IF (
[Average] < 0.9,
"Yellow ball",
IF ( [Average] > 1.1, "Red ball", "Green ball" )
)
Measure:
Count =
CALCULATE (
COUNT ( 'Average'[Item] ),
FILTER ( ALL ( 'Average' ), [Ball collour] <> "Green ball" )
)
Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Again:
Since there is one yellow ball I made % both including Yellow and not including yellow. Please see file link.https://drive.google.com/file/d/1fw1_03W0Pn_eOG8CdKhOvSiwVrcXrGru/view?usp=sharing
Hi:
What are the expected results?
Can you share some example data?
* If you wants a Daily or Monthly average it is important to have a date table, marked as a date table (on date field). I'll paste some DAX Date table code below, in case you need it.
Go to Modeling>NEW Table: (see below).
Now measures after you get your date table set and connected to your fact table on date fields.
example of measure we want average on:
Total Sales = SUM(Table[Sales Amt])
[Daily Sales Average] =averagex( Dates', [Total Sales])
[Daily Sales Average] =
averagex(
'Dates',
calculate(
[Total Sales],
Transaction_data[Sales_amount] <> 0
)
)
[Monrthly Sales Average] =
averagex(
'Dates'[Month],
calculate(
[Total Sales],
Transaction_data[Sales_amount] <> 0
)
)
Dates =
-- Specify a start date and end date
VAR StartDate = Date(2021,1,1)
VAR EndDate = Today() + 243
VAR FiscalMonthEnd = 12
-- Generate a base table of dates
VAR BaseTable = Calendar(StartDate, EndDate)
-- Add the Year for each individual date
VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))
-- Add the calendar month and other month related data for each date
VAR Months = ADDCOLUMNS(
Years,
"Month",MONTH([Date]),
"Year and Month Number",FORMAT([Date],"YYYY-MM"),
"Year and Month Name",FORMAT([Date],"YYYY-MMM"),
"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),
"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),
IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))
)
-- Add the Quarter and other quarter related data for each date
VAR Quarters = ADDCOLUMNS(
Months,
"Quarter",ROUNDUP(MONTH([Date])/3,0),
"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))
-- Add the Day and other day related data for each date
VAR Days = ADDCOLUMNS(
Quarters,
"Day",DAY([Date]),
"Day Name",FORMAT([Date],"DDDD"),
"Day Of Week",WEEKDAY([Date]),
"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)
-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date
VAR Weeks = ADDCOLUMNS(
Days,
"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,
"Week of Year (Sunday)",WEEKNUM([Date],1),
"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))
-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.
var WorkingDays = ADDCOLUMNS(
Weeks,
"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})
RETURN WorkingDays
Hello, I have a primary table:
Ball Logics: if(% < 90% Yellow ball, % > 110% Red ball, % between 90% and 110% green ball)
Date | Item | % | Ball collour |
26/04/2022 | Item A | 113,9% | Yellow ball |
13/05/2022 | Item A | 97,7% | Green ball |
16/05/2022 | Item A | 97,7% | Green ball |
18/05/2022 | Item A | 85,0% | Red ball |
25/04/2022 | Item A | 100,3% | Green ball |
25/05/2022 | Item A | 100,3% | Green ball |
27/04/2022 | Item A | 100,3% | Green ball |
18/05/2022 | Item A | 81,0% | Red ball |
18/05/2022 | Item A | 89,0% | Red ball |
26/04/2022 | Item B | 113,9% | Yellow ball |
13/05/2022 | Item B | 97,7% | Green ball |
16/05/2022 | Item B | 97,7% | Green ball |
18/05/2022 | Item B | 85,0% | Red ball |
25/04/2022 | Item B | 100,3% | Green ball |
26/04/2022 | Item B | 100,3% | Green ball |
27/04/2022 | Item B | 91,0% | Green ball |
18/05/2022 | Item B | 70,0% | Red ball |
18/05/2022 | Item B | 50,0% | Red ball |
From this table, I make a avarage table:
Average | % | Ball collour |
Item A | 96,1% | Green ball |
Item B | 89,5% | Red ball |
I need to count in this Avarage table how many itens appears different of green ball, can you help me?
thanks a lot.
Hello Again:
Since there is one yellow ball I made % both including Yellow and not including yellow. Please see file link.https://drive.google.com/file/d/1fw1_03W0Pn_eOG8CdKhOvSiwVrcXrGru/view?usp=sharing
Hi, @vinicius_ramos
You can try the following methods.
Table:
Average =
SUMMARIZE (
'Table',
'Table'[Item],
"Average",
CALCULATE (
AVERAGE ( 'Table'[%] ),
FILTER ( ALL ( 'Table' ), [Item] = EARLIER ( 'Table'[Item] ) )
)
)
Column:
Ball collour =
IF (
[Average] < 0.9,
"Yellow ball",
IF ( [Average] > 1.1, "Red ball", "Green ball" )
)
Measure:
Count =
CALCULATE (
COUNT ( 'Average'[Item] ),
FILTER ( ALL ( 'Average' ), [Ball collour] <> "Green ball" )
)
Is this the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello:
Glad it worked. Are you able to mark my reply with solution or kudos? I appreciate that. Thanks again..
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 |
---|---|
54 | |
25 | |
23 | |
15 | |
11 |
User | Count |
---|---|
77 | |
61 | |
47 | |
18 | |
12 |