Sales 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 model
Longitudinal injuries - how many players are injured at any timepoint
Hi all, at the moment I am working on injury data over multiple seasons. Key information for everyone is: how many players do you have available. Therefore, we want to see how many players are injured at any moment in time (is it generally 3 or 8 players that you can't use). Therefore I want a graph as shown below (made in Excel): over the timeframe I want the # of players that are injured. For this purpose I have the date of occurence and date of return-to-play. In the graph you can see on the x-axis the date and on the y-axis the amount of injuries for each team (team is different colors). For example: a player in the 'blue' team is injured from 1st of June till 3rd of August --> he should be counted as 'one injury' for the whole time-period, not just the start of his injury. In addition to this player more players in this team become injured and add up. I have already tried distinctcount, count or datesbetween combinations of DAX formulas, but I am too much of a beginner to get it working. I also tried to generate a new table as 'count', with date filters and summarizing countrows, but everything I try: it is not valid, unfortunately. Table = VAR ExpandedTable = GENERATE( CALENDAR(DATE(2010,1,1),DATE(2025,12,31)), FILTER( 'Blessures', [Date]>='injuries'[start_injury] && [Date]< IF(ISBLANK('injuries'stop_injury),TODAY(),'injuries'stop_injury) ) ) RETURN SUMMARIZE( ExpandedTable, [Date] , [Team], "Count",COUNTROWS('injuries') ) I hope I showcased the problem clearly. If anything is still unclear, please let me know! Thanks in advance!
