create area chart with green and red colors to indicate profit and loss
I have created area chart that that shows profit and loss % over time. I would like to show the loss (negative part) in red color and the profit ( positive part) in geen color. i created 2 measures one for profit % and one for loss % positive p&l = var pl=[p&l %] var result= IF(pl>0 , pl,0) return result negative p&l = var pl=[p&l %] var result= IF(pl<0 , pl,0) return result when i added the measures in the chart it looked like: any idea on how to create the area chart with green/red colors instead of 1 color
Count person who are joining and leaving
Hi. Seeking for your help regarding DAX. I want to create a DAX that counts the people who join and leave the online event. I have created a visual that counts who joins in a specific hour. However, I wish to make a visual that adds the people to the count who join the online event and subtracts the number of people who leave in an hourly interval. Below is the sample data Name start_time end_time duration (seconds) Duration (Minutes) Duration (Hour) PERSON_1131 12/03/2024 6:48:55 am 12/03/2024 6:48:56 am 1 0 0 PERSON_1282 12/03/2024 6:49:10 am 12/03/2024 6:49:19 am 9 0.2 0 PERSON_1320 12/03/2024 6:49:26 am 12/03/2024 6:49:34 am 8 0.1 0 PERSON_1326 12/03/2024 6:50:57 am 12/03/2024 6:51:06 am 49 0.8 0 PERSON_1107 12/03/2024 6:53:47 am 12/03/2024 6:53:53 am 6 0.1 0 PERSON_1076 12/03/2024 6:58:49 am 12/03/2024 6:58:51 am 2 0 0 PERSON_1004 12/03/2024 6:49:12 am 12/03/2024 6:59:40 am 1028 17.1 0.3 PERSON_1217 12/03/2024 7:00:39 am 12/03/2024 7:00:44 am 5 0.1 0 PERSON_1298 12/03/2024 7:00:24 am 12/03/2024 7:00:44 am 20 0.3 0 PERSON_1217 12/03/2024 7:00:56 am 12/03/2024 7:00:59 am 3 0 0 PERSON_1426 12/03/2024 6:50:25 am 12/03/2024 7:01:00 am 5075 84.6 1.4 PERSON_1326 12/03/2024 6:51:14 am 12/03/2024 7:01:07 am 4993 83.2 1.4 PERSON_1081 12/03/2024 7:01:20 am 12/03/2024 7:01:23 am 3 0 0 PERSON_1426 12/03/2024 7:01:21 am 12/03/2024 7:01:24 am 3 0 0 PERSON_1326 12/03/2024 7:01:12 am 12/03/2024 7:01:57 am 45 0.8 0 PERSON_1130 12/03/2024 7:02:05 am 12/03/2024 7:02:08 am 3 0 0 PERSON_1006 12/03/2024 7:02:13 am 12/03/2024 7:02:14 am 1 0 0 PERSON_1068 12/03/2024 7:00:54 am 12/03/2024 7:02:18 am 164 2.7 0 PERSON_1231 12/03/2024 7:02:32 am 12/03/2024 7:02:35 am 3 0 0 PERSON_1076 12/03/2024 7:02:42 am 12/03/2024 7:02:44 am 2 0 0 PERSON_1060 12/03/2024 7:02:50 am 12/03/2024 7:02:51 am 1 0 0 PERSON_1147 12/03/2024 7:02:19 am 12/03/2024 7:02:55 am 36 0.6 0 PERSON_1210 12/03/2024 7:03:19 am 12/03/2024 7:03:20 am 1 0 0 PERSON_1564 12/03/2024 7:02:51 am 12/03/2024 7:03:26 am 75 1.2 0 PERSON_1581 12/03/2024 7:01:39 am 12/03/2024 7:03:36 am 197 3.3 0.1 PERSON_1051 12/03/2024 7:03:34 am 12/03/2024 7:03:48 am 14 0.2 0 PERSON_1074 12/03/2024 7:04:29 am 12/03/2024 7:04:31 am 2 0 0
Total Bar over Months
Greg_Deckler amitchandak I am trying to get a graph divided by months to show a total bar. When I use a similar dax formula by categories to show a total bar, it works fine. When I try to create a table in Excel (upstream) or within Power BI, it either shows the months out of order, or shows the months in order but no total bar. ScheduledAppts_Total = SWITCH( TRUE(), SELECTEDVALUE( TotalBar_MY[Month Year]) = "Total" , CALCULATE( SUM( 'Fact Epic'[Scheduled Appointments] ), ALL( TotalBar_MY[Month Year] ) ) , SUM('Fact Epic'[Scheduled Appointments]) ) I know something similar works when I use it over categories: Below I have shown the table view and the report view. Here is the DAX for the other bar graphs, which show the total over categories successfully. CSQ_Total_Bar = UNION(DISTINCT('Fact Cisco'[CSQ Name]),{"Total"}) For Abandonment Rate Total_AbandonmentRate = SWITCH(TRUE(), SELECTEDVALUE(CSQ_Total_Bar[CSQ Name]) = "Total" , CALCULATE(Average('Fact Cisco'[Abandonment Rate]), ALL(CSQ_Total_Bar[CSQ Name])) , Average('Fact Cisco'[Abandonment Rate])) For Service Level Total_CSQ_SVL = SWITCH(TRUE(), SELECTEDVALUE(CSQ_Total_Bar[CSQ Name]) = "Total" , CALCULATE(Average('Fact Cisco'[Service Level]), ALL(CSQ_Total_Bar[CSQ Name])) , Average('Fact Cisco'[Service Level])) I have tried several different ways that I've seen on youtube, but nothing seems to work.distinct count of users who have their sales in each month in last six-month
Dear Community, I have 3 years of sales data of users. I have need to find the no. of users who have their sales each month in last 6 month. Here is the sample data: User Code Sales Qunatity Date 33771 10 12-03-2024 33774 10 11-03-2024 33796 10 28-02-2024 33802 10 09-01-2024 33802 10 13-03-2024 33803 10 27-11-2023 33803 10 13-09-2023 33842 10 28-02-2024 33852 10 01-08-2023 33854 10 18-02-2024 33854 10 25-02-2024 33890 10 14-02-2024 33901 7.5 03-01-2024 33908 7.5 20-10-2023 43868 7.5 16-01-2024 44360 7.5 09-12-2023 33774 7.5 05-10-2023 57557 7.5 23-02-2024 57824 7.5 04-10-2023 58818 7.5 16-02-2024 70852 7.5 13-11-2023 33774 7.5 25-02-2024 33774 9 20-01-2024 3488 11.25 30-09-2023 48268 11.25 10-01-2024 14820 25 28-02-2024 14889 25 05-10-2023 14890 25 27-02-2024 3312 25 10-02-2024 33599 25 09-01-2024 33774 25 29-09-2023 33774 25 06-03-2024 33790 25 12-03-2024 33796 7.5 04-02-2024 33796 7.5 24-01-2024 33774 11.25 20-10-2023 33812 11.25 30-11-2023 33838 10 08-11-2023 33841 10 06-11-2023 33871 10 27-02-2024 33871 10 22-01-2024 33890 10 18-08-2023 33908 7.5 18-12-2023 33908 7.5 12-11-2023 33915 7.5 31-12-2023 33927 7.5 15-01-2024 33927 8 30-11-2023 33929 8 02-01-2024 33948 11 06-03-2024 33948 14 13-01-2024 33964 50 13-01-2024 33771 19 12-03-2024 33802 19 10-02-2024 33803 19 06-03-2024 33803 19 30-09-2023 33842 19 14-10-2023 33852 19 28-02-2024 33854 19 04-10-2023 33854 19 16-02-2024 33854 19 19-11-2023 33890 19 12-03-2024 33892 19 14-12-2023 33774 19 30-12-2023 33908 19 03-11-2023 33908 19 11-02-2024 33914 19 06-02-2024 33915 19 23-12-2023 44360 19.95 26-12-2023 70852 19.95 07-10-2023 33774 19.95 03-11-2023 3488 19.95 09-08-2023 3488 19.95 18-12-2023 48268 19.95 12-12-2023 14782 19.95 16-01-2024 14782 19.95 25-08-2023 14820 19.95 13-02-2024 14889 19.95 08-02-2024 14890 22.05 26-12-2023 3312 22.05 30-11-2023 33774 22.05 30-11-2023 33803 23.1 13-09-2023 33812 23.1 12-11-2023 33841 23.1 31-12-2023 33871 23.1 15-01-2024 33871 23.1 12-01-2024 33901 23.1 30-10-2023 33901 23.1 12-03-2024 33908 23.1 11-02-2024 33915 23.1 05-11-2023 33915 23.1 09-03-2024 33774 23.1 09-12-2023 33927 23.1 08-01-2024 33929 23.1 09-11-2023 33948 23.1 02-03-2024 33948 23.1 31-12-2023 33948 23.1 23-10-2023 33984 23.1 06-09-2023 34013 23.1 27-02-2024 42739 23.1 05-03-2024 45148 23.1 14-02-2024 58092 23.1 09-12-2023 66212 23.1 01-11-2023 66217 23.1 01-03-2024 55243 24.15 25-01-2024 121955 25.2 12-08-2023 14890 26.25 08-11-2023 3336 26.25 28-02-2024 3346 26.25 04-10-2023 33599 26.25 11-01-2024 3367 26.25 05-11-2023 3404 26.25 12-03-2024 3446 26.25 18-02-2024 3489 26.25 13-11-2023 43056 26.25 09-02-2024 43868 26.25 02-02-2024 44342 26.25 02-02-2024 44360 26.25 08-09-2023 33796 26.25 27-02-2024 33802 26.25 07-03-2024 33803 26.25 14-12-2023 33812 26.25 09-02-2024 33812 26.25 31-08-2023 and this is the no. of users that i need to find based on sample data in power bi Thanks!DAX in building Rolling 4 Week Average
Hi, I need help in building 4 week rolling average for "Net Change in Price and Cost" with below sample data Change in Price, Change in Cost and Net Change in Price and Cost are all 3 measures. Net Change in Price and Cost = Change in Price - Change in Cost I do have separate date table and the above data shows weekly data and my report has Invoice Date as Filter. I need to calculate rolling 4 week average, here the tweak is that for first week start date 12/07 rolling average should be same while for next 12/14 it should (-4.80% + 0.18%)/2 = -2.31% for 12/21 it should be (-4.80% + 0.18% - 0.04%)/3 = -1.55% for 12/28 it should be (-4.80% + 0.18% - 0.04% -0.65%)/4 = -1.33% for 01/04 it should be (0.18% - 0.04% -0.65% +1.03%)/4 = 0.13% as shown below I have created below DAX but its not working as expected Net Change in Price and Cost 4 Wk Rolling Avg = var start_day = MIN('Date'[Week Start Date])-21 var end_day = MAX('Date'[Week Start Date]) return CALCULATE([Net Change in Price and Cost], DATESBETWEEN('Date'[Week Start Date],start_day,end_day), REMOVEFILTERS('Date'[Week Start Date]))/CALCULATE(DISTINCTCOUNT('Date'[Week Start Date]),DATESBETWEEN('Date'[Week Start Date],start_day,end_day),REMOVEFILTERS('Date'[Week Start Date])) Can someone help me on this??Handling Subtotals for Pre-Calculated (Non-Additive) Measures
I'd like to share a neat pattern that I've discovered recently. Motivation I'm building a report where I'd like to use a small amount of summarized data from another report I've built that has a large and complex data model. In my particular case, I want to pull IRR values at several levels of granularity. The large complex model has measures that allow me to dynamically generate summary tables for whatever configuration of row and column granularities I choose. Here's an example with Category and Subcategory on the rows and Group on the columns. In the new report I'm building, this matrix is exactly what I need. I don't need the flexibility to dynamically choose other granularities to report on and I don't want to bog down my report with all the memory or computational overhead needed to do so. As a result, I've chosen to import this data into my new report by querying the existing complex data model. (I do this by connecting to the complex model dataset as an Analysis Server with DAX query. Refer to this question for a bit more detail.) Query My first thought is to write a simple query like this EVALUATE SUMMARIZECOLUMNS ( ComplexModel[Category], ComplexModel[Subcategory], ComplexModel[Group], "IRR", [IRR] ) The result looks like this: This works fine for all of the non-bold numbers in the matrix above but there's no way to generate the subtotals and grand totals (all the bold numbers in the matrix) since it's only returning the IRR at the lowest level of granularity and these returns cannot be summed or averaged or otherwise aggregated/combined to get the subtotals I'm interested in. My solution to this is to use the handy ROLLUPADDISSUBTOTAL functionality within SUMMARIZECOLUMNS. EVALUATE SUMMARIZECOLUMNS ( ROLLUPADDISSUBTOTAL ( ComplexModel[Category], "IsCategoryRollup", ComplexModel[Subcategory], "IsSubcategoryRollup" ), ROLLUPADDISSUBTOTAL ( ComplexModel[Group], "IsGroupRollup" ), "IRR", [IRR] ) Note: Category and Subcategory are together since the latter is always a subset of the former. The result looks like this Notice the rows with blanks in the first three columns, which correspond to subtotals over those dimensions. This updated query has all of the values we need but how do we use it? Measure Suppose we use a simple measure like this: Simple Measure = SELECTEDVALUE ( Summary[IRR] ) This actually gets us all of the values we need but doesn't display them like I want (i.e. blank value rows and columns instead of subtotal rows and columns. This is what it looks like if we use it in a visual with columns from the Summary table: Savvy DAX folks are probably aware of the function ISINSCOPE I'll use to resolve this. If not, check the related articles linked from its DAX Guide page. My first attempts at this measure looked like some variation of a measure like this: Verbose Measure = IF ( ISINSCOPE ( Summary[Subcategory] ), IF ( ISINSCOPE ( Summary[Group] ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = FALSE (), Summary[IsCategoryRollup] = FALSE (), Summary[IsGroupRollup] = FALSE () ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = FALSE (), Summary[IsCategoryRollup] = FALSE (), Summary[IsGroupRollup] = TRUE () ) ), IF ( ISINSCOPE ( Summary[Category] ), IF ( ISINSCOPE ( Summary[Group] ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = TRUE (), Summary[IsCategoryRollup] = FALSE (), Summary[IsGroupRollup] = FALSE () ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = TRUE (), Summary[IsCategoryRollup] = FALSE (), Summary[IsGroupRollup] = TRUE () ) ), IF ( ISINSCOPE ( Summary[Group] ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = TRUE (), Summary[IsCategoryRollup] = TRUE (), Summary[IsGroupRollup] = FALSE () ), CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsSubcategoryRollup] = TRUE (), Summary[IsCategoryRollup] = TRUE (), Summary[IsGroupRollup] = TRUE () ) ) ) ) Note: You may notice that there are only six scope combinations compared to the total possible 2³ = 8 for the three dimensions, Category, Subcategory, Group. The reason is that I have excluded the cases where Category is rolled up but Subcategory is not. This measure does work but it's ugly and tedious to write. Fortunately, I noticed the relationship between Is-In-Scope and Is-a-Rollup. Whenever a dimension is in scope, select the rows that are not rollups of that dimension. Stated in reverse, select the rollup rows when a dimension is not in scope. This leads to a much more elegant version of the measure: Smarter Measure = VAR IsCategoryRollup = NOT ISINSCOPE ( Summary[Category] ) VAR IsSubcategoryRollup = NOT ISINSCOPE ( Summary[Subcategory] ) VAR IsGroupRollup = NOT ISINSCOPE ( Summary[Group] ) RETURN CALCULATE ( SELECTEDVALUE ( Summary[IRR] ), Summary[IsCategoryRollup] = IsCategoryRollup, Summary[IsSubcategoryRollup] = IsSubcategoryRollup, Summary[IsGroupRollup] = IsGroupRollup ) Using this measure in a matrix visual with Summary[Category], Summary[Subcategory] on the rows and Summary[Group] on the columns now looks just like the original matrix visual within the Complex Model. Neat, eh?Find MAX QTR within a Year based on Criteria
Hi Team, Need to find the MAX value for a year based on the Criteria. In the below table, we have values for COuntry & Brand combincation appearing for each QTR, need to find the MAX QTR for 2023 and likewise for 2024. Highlighted Calculated Column is what I need. If Italy Ferrari has 3 QTR, 1, 2 & 3 then against Q3 2023, it should put 2023, likewise for 2024. Thanks!
Create date table to get Week Number and Quarter number
Hi Community, Requirement: Im actually stuck in calculating Weeknumber & QuarterNumber. Based on the min & max date in the Aria(Table) date column it should consider the week 1 start from min date & when next year comes up it should continue rather than re-start from 1st week? E.g. 25 july is minmum date then 1 week & 3 jan 2024 is max date then 23rd week. 1. Below is the Calculated date table. The WeekNumber should not restart from Jan-01-2024 instead it should continue from the last calculation as week-23. How? 2. How to calculate QuarterNumber by considering the same min & max date method ? The quarter should start with Q1 instead of Q3 for minimum date & so on calculate till max date. Pbix File Link Time_Period = VAR _StartDate = MIN ( 'ARIA'[EVENT_DAY] ) VAR _EndDate = MAX ( 'ARIA'[EVENT_DAY] ) VAR _WeekStart = WEEKNUM ( _StartDate ) RETURN ADDCOLUMNS ( CALENDAR ( _StartDate, _EndDate ), "Year", YEAR ( [Date] ), "Month", FORMAT ( [Date], "mmmm" ), "MonthNum", MONTH ( [Date] ), "Quarter", ( "Q" & FORMAT ( [Date], "Q" ) ), "DayOfWeekName", FORMAT ( [Date], "dddd" ), "WeekNumber", IF ( [Date] >= DATE ( YEAR ( [Date] ), 8, 1 ), QUOTIENT ( [Date] - DATE ( YEAR ( [Date] ), 8, 1 ), 7 ) + 2, 1 ) ) Thanks in advance!
Calculate Sum and Filter with IF conditions
Hi, I have a monthly trend table where I need to calculate the sum of patients with few filters and conditions applied. See the below measure - Patients_Adj = var test = CALCULATE(SUM(Patients[Patients]),Patients[TYPE] = "Paid", Patients[PRODUCT_FK] = "Product A") return IF(MAX('Date'[Month])>=[max_Date],test,0) This measure is giving correct output at row level, but totals are incorrect because this is doing the aggregation first and then applying the return condition. Is there a way I can re-write this measure so that conditions are applied first and then the sum aggregation happens. This is important because I need to calculate the cumulative number on top of this measure so that totals has to be accurate. Below is the table that I am using - Thanks!
