Get the average of a column value based on total count of values in ID column
Hi, In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column. Each object will be having multiple runid's which is captured in "RunID" column which is a unique value. For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333". Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column. For eg: The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10. The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6. The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8. Sample screenshot for above query: So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10. How to achieve this using DAX query?? I am connecting to this table in powerbi via SQL direct query mode. Thanks.
Last N months from selected month
Hi Team, Please do needful for the following criteria. I have the below kind of data, the requirement i need is , if we select May, we need to show last four months data(Feb, Mar, apr, May) . and it should be based on year also(Ex:- if we select 2022 feb, then result is 2021 Nov, 2021 dec, 2022 jan, 2022 feb) Thanks in Advance.
DAX to compare two columns and set color for the KPI column
Hi All, I have an employee table with Salary ,employee and benchmark columns . I need to compare the benchmark column with Salary column and if Salary > benchmark then KPI color column should be Green, If Salary < benchmark then KPI color column should be Red and if Salary = benchmark then KPI color column should be Yellow. Could you please suggest any DAX to handle this ? I need to show the KPI color column as shown below in expected output screenshot. My Source input Table is as below :- Salary Benchmark KPIColor Employee 20000 15000 Green color should be filled E1 10000 15000 Red color should be filled E2 15000 15000 Yellow color should be filled E3 Expected output :- Kind regards SameerDAX Query to find Sum of column values per month
Hi All, I have a table say " Website" and have three columns as below "Ranking","Clicks" and "date" :- I need to find the sum of the Clicks and Ranking column for present month "August" and previous month "July". It should happen for all upcoming months Ranking Clicks date 1 2000 02.08.2021 2 1200 01.08.2021 1 2300 01.08.2021 3 1000 31.07.2021 4 900 29.07.2021 -1 200 28.07.2021 -2 100 27.07.2021 1 2400 26.07.2021 2 2100 25.07.2021 4 1300 24.07.2021 1 1800 21.07.2021 Expected values are in yellow : I want DAX query to return the sum of the Ranking and Clicks for Current month and previous month after extracting month value from date column. I tried to use the below DAX for finding sum of click and reputation, but I got this error :- current_month_click = CALCULATE(SUM('Website'[Click]),FILTER('Website',MONTH('Website'[Month])=MONTH(TODAY()))) last_month_click= var current_month= MONTH(TODAY()) return CALCULATE(SUM('Website'[Click]),FILTER('Website',MONTH('Website'[Month])=current_month -1)) But it is not working, can someone suggest any modification or any DAX to handle this . Kind regards SameerDAX to calculate average benchmark of the ratings
Hi All, The rating of a vendor company is marked from the range 5 to -5 by customers as in the below source table. Rating column defines the rating per company per date and this date is for the whole year .I have added as a sample data for date in the below given range. I need to find the average Benchmark for ratings of the total companies[A,B,C,D] as stated below :- How can i find the average benchmark number for rating of the total companies in the market [A,B,C,D]? Could anyone suggest any DAX calculated measure or column to find this average benchmark in the below case scenario? Rating Company Date 2 A 14.07.2021 2 A 15.07.2021 2 A 13.07.2021 1 A 12.07.2021 -1 A 11.07.2021 0 A 10.07.2021 1 B 14.07.2021 1 B 15.07.2021 4 B 13.07.2021 2 B 12.07.2021 2 B 11.07.2021 -1 B 10.07.2021 1 C 14.07.2021 1 C 15.07.2021 4 C 13.07.2021 -2 C 12.07.2021 2 C 11.07.2021 3 C 10.07.2021 1 D 12.07.2021 4 D 11.07.2021 3 D 10.07.2021 Kind regards Sameer
