Perform a double aggregate with DAX
Dear forum, i'm struggling with a solution which I expect to be easy but can't seem to wrap my head around within DAX. My desired output is to have a dynamic calculation that can be filtered in PowerBI with the regular relationships and underlying data but is aggregated twice. The first aggregate is to calculate how many products a single customer has taken, given a specific timeframe and possible other filters. The second aggregate is to group these customers into clusters to have an output similar to the following: Desired output: # Amount of customers # Amount of products taken 25.421 0 Products 49.572 1 to 3 Products 12.451 4 to 6 Products 6.021 7 to 9 Products 3.212 10 to 12 Products 1.421 > 12 Products How do I generate a measure (and maybe some additional columns) to create such an output? I have added an example dataset to this topic to experiment with. In a second question, i would like to also be able to aggregate the data into years since a customer has entered vs the product taken date. An example of this output would be: # amount of products taken Years since customer start 1503 0 232 1 24 2 10 3 Datamodel example: Example data CustomerID Customer_Since A 1-1-2021 B 1-2-2021 C 1-2-2022 D 1-6-2022 E 1-8-2022 F 1-10-2022 G 1-12-2023 Example Product data: ID ProductID CustomerID Product_Date 1 1 A 3-1-2021 2 2 A 12-1-2023 3 3 B 1-5-2021 4 3 B 1-10-2021 5 6 C 1-1-2024 6 10 E 1-6-2023 7 13 E 1-8-2023 8 15 E 1-10-2023 9 18 E 1-12-2022 10 1 F 1-6-2023 11 21 F 1-8-2023 Note: the desired output is just fictive, it does not correspond with the provided example data. The link to the example PBIX dataset: Example-Set.pbixSales Amount at transaction level (aggregate at higher level) measure
Hello, I am trying to create a measure that displays the Sales Amount at transacttion (opportunity) level, regardless of the other dimensions I pull in my report. This can be a calculated column and it will work just fine, but I need to use a parameter with this calculation in the future so it HAS TO BE A MEASURE. When I do this calculation: Sales Amount Oppty Level = CALCULATE([Sales Amount],REMOVEFILTERS(Prod[ProdName]),VALUES(Oppty[OpptyName])) i get the accurate amount at opportunity level, but, if i add dimensions to the report, I get a cartesian product (all dimension values x all dimension values). ALLEXCEPT works only when I use the Product Table Oppty Name dimension, but I need to use Oppty Name from Product Table and the Partner dimension will make cartesian product with Opportunity for some reason. Cannot Use Oppty Name from Oppty table since then the measure will show the total sales amount for all Oppty for every row. Sales Amount Oppty Level 2 = CALCULATE([Sales Amount],ALLEXCEPT(Prod,Prod[OpptyName])) If i switch ALLEXCEPT to the below: Sales Amount Oppty Level 3 = CALCULATE([Sales Amount],ALLEXCEPT(Oppty,Oppty[OpptyName])) I will get the Sales Amount, not the Oppty total for all rows. Please help me to see the total Oppty Amount in a visual where I can have Oppty Name, Prod Name, Partner without cartesian product, like (red is bad, since partners 1 and 2 are not connected to oppty 1): Please help! Here is the modelMax Function Aggregate
Hello all, I'm having some issues with a training tracker that I am building, I want to track progress of students from 'tests' that they carry out. Test1 = 10% Test2 = 20% Test3 = 30% Test4 = 20% Test5 = 20% Total = 100% Each subject has numerous subcategories within it which build up to an overall percentage within this subject, one issue is that the students can complete the same tests multiple times and I only want to identify one entry so I have used the below MAX fuction: %Trained = MAX ( 'Results'[Test1%] ) + MAX ( 'Results'[Test2%] ) + MAX ( 'Results'[Test3%] ) + MAX ( 'Results'[Test4%] ) + MAX ( 'Results'[Test5%] ) This worked to gain the correct percentage for one individual subcategory but when it came to aggregating over the numerous subcategories the max function is throwing off the calculation. This is what I tried to use: %TrainedAggregate = Divide( MAX ( 'Results'[Test1%] ) + MAX ( 'Results'[Test2%] ) + MAX ( 'Results'[Test3%] ) + MAX ( 'Results'[Test4%] ) + MAX ( 'Results'[Test5%] ) , [CountSubjectSubcategory]) I have changed the information due to sensitivity, The first image as you can see gives the figure I would expect, on the second however when I am aggregating over 12 subcategories it is only picking up 100% as the max when in theory it would be 1200%.
DAX that works with/rolls up to different dimensions
Hello All, I have had a look for this topic and can't find any discussion about it so i'm either the first to ask or my search paremeters are shocking. Either way, I need some help! I have a requirement to compare a sum total to the same day in the previous week to provide a week on week variance. This is an amendment to an existing report in my business that was originaly aggregating numbers per week. Now the requirements is to look at these figures each day of the week but it still needs to be aggreagated for the week. This is where i am struggling as I can either get it to work for each day or for the week as a total. I've tried simply using DATEADD but this always results in an error (returns multiple results when it expects one). My source data is an aggregated view that appears as follows: This is linked with a DIM_DATE table with many columns but the ones used in my report are the Calendar_Date (for the relationship to the KPIs table shown above), [Start of Week], [FISCAL_WEEK], [WEEK_DAY_NAME]. The normal SUM DAX for the Gross Orders per day is fine (as you'd expect), but my WOW% comparison is being a pain: My current DAX is as follows: Orders WOW Growth = VAR SumOrders = SUMX ( SALES_KPIS, SALES_KPIS[GrossOrders] ) VAR PreviousFiscalWeek = MAX ( DIM_DATE[FISCAL_WEEK] ) - 1 VAR WeekDayName = SELECTEDVALUE ( DIM_DATE[WEEK_DAY_NAME] ) VAR OrdersPreviousWeekDay = CALCULATE ( SUM ( SALES_KPIS[GrossOrders] ), FILTER ( ALL ( DIM_DATE ), DIM_DATE[FISCAL_WEEK] = PreviousFiscalWeek ), DIM_DATE[WEEK_DAY_NAME] = WeekDayName ) VAR OrdersPreviousWeek = CALCULATE ( SUM ( SALES_KPIS[GrossOrders] ), FILTER ( ALL ( DIM_DATE ), DIM_DATE[FISCAL_WEEK] = PreviousFiscalWeek ) ) VAR GrowthPercentage = IFERROR ( ( SumOrders - OrdersPreviousWeekDay ) / OrdersPreviousWeekDay, BLANK () ) RETURN GrowthPercentage If i swap out the variable 'OrdersPerviousWeekDay' with 'OrdersPreviousWeek', i do get the figure for the week but it also gives me the weekly total for each day which is not what i need! Is there anybody on here that can help point me in the right direction. I've tried using a combination of SWITCH and IF commands as well but return errors each time! I've been trying to get this to work for a day and its now time to raise the white flag. Any help will be appreciated! Thanks, Chris
How to use "Countdistinct" in this scenario?
Hello friends, I'm still a beginner and I was performing an assessment which asked me to use "COUNTDISTINCT" to get the number of stores each product was sold in. This is the full table in the data view: And here is the final solution: Can you help me understand how to get the number of stores sold in to look as same as we see in the solution ? Thanks in advance!
