How do i find a maximum value based on 2 columns?
Hi There I am trying to figure out how to pull out a calculation called 'based units' which would be the MAX of units non promo but it needs to be by EAN_item and MrktID and NOT period_key as i am trying to evaluate a consistant units sold week on week that wasn't on promotion. It has to stay consistant through every period. Below is an example of the data and wondered if anyone knows what query to use? I've tried CALCULATE MAX across ALL Periods but this wouldn't put product and mrkt into context. Any ideas on how i can do this? The end goal would to get to something like this (and if total units sold is less than base it would equal total units) with incremental units being (total units - incremental units) : Many Thanks! 🙂
max measure is showing infinity value
hello i created 2 measures to find the max and min percentage of another measure. the dax code to create the 2 measures is the same the only difference is in the minimum measure i used (minx) and in the maximum measure i used (max). although the 2 measures are exactly the same , the max measure it showing me (infinity ) in case the percentage is greater than 100% and the minimum function is working properly. i cant understand why it's showing inifinty and not the correct percentage this the code for the minumum percentage measure Min New Achievement % overall = CALCULATE( minX(SUMMARIZE('categories trans','categories trans'[AGENT_ID],'categories trans'[AGENT_NAME],'categories trans'[TRANSDATE (bins)],'categories trans'[base type without zeed],'categories trans'[categories summarize],'Agents list'[Position],'categories trans'[CHANNEL],'Agent Rate Key'[Agent Rate]),[achiev of overall]),ALL('Agents list'[Position]),ALL('categories trans'[categories summarize],'categories trans'[base type without zeed]),'categories trans'[achievement],ALL('Agent Rate Key'[Agent Rate]),'categories trans'[categories summarize]<>BLANK()) this is the code for the max percentage measure Max New Achievement % overall = CALCULATE( maxX(SUMMARIZE('categories trans','categories trans'[AGENT_ID],'categories trans'[AGENT_NAME],'categories trans'[TRANSDATE (bins)],'categories trans'[base type without zeed],'categories trans'[categories summarize],'Agents list'[Position],'categories trans'[CHANNEL],'Agent Rate Key'[Agent Rate]),[achiev of overall]),ALL('Agents list'[Position]),ALL('categories trans'[categories summarize],'categories trans'[base type without zeed]),'categories trans'[achievement],ALL('Agent Rate Key'[Agent Rate]),'categories trans'[categories summarize]<>BLANK()) the is the [achiev of overall] measure achiev of overall = [core achievement]+[access achiev 10%] this is the result max measure should show the value 149.82% instead of infinity note that i had to use summarize function as i want to show the min/max achievement percentage per agent, category, category type, and position and then find the maximum and minimum value percentage among all categories.Remove Table Filters While Using Virtual Table Measures
Hello! I'm currently trying to return the max value from a virtual table that I will use in another virtual table, along with removing the table filters when I place this max value in a table visual. Specifically, the 'MAX Efficiency Score' measure will be utilized in the 'Efficiency Score' measure (see 'var table7' in the 'Efficiency Score' measure). Efficiency Score = var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference]) var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0) var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet])) var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders])) var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio])) var table7 = ADDCOLUMNS(table6, "FinalScore", [Score]/[MAX Efficiency Score]) var table8 = SUMX(table7, [Score]) return table8 MAX Efficiency Score = var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference]) var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0) var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet])) var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders])) var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio])) var final = ADDCOLUMNS(table6, "MaxValue", MAXX(table6, [Score])) return CALCULATE(MAXX(final, [MaxValue]), ALL('Fuel (Traveling)')) My current issue is that I can't get the 'MAX Efficiency Score' measure to return the max score of 79.58 (in the case below) for each row of my table visual below. For background, the table visual below contains the same fields (Driver Fleet Name and DriverName/ID) that are used in the SUMMARIZE functions above. I also set the dashboard up so the 'Efficiency Score' and 'MAX Efficiency Score' values dynamically update when the date slider is used, which is why I referenced the 'Combined Calendar[Date - Copy] field in the measures above. I'd really appreciate any help I could get on this issue. Thanks!Max date by group, rewrite measure to create calculated column
I have the following calculated table from this code: Tbl = SUMMARIZECOLUMNS( dAssets[Ticker], dDates[Date], "Purchases", [Shares purchased], "Sales", [Shares sold] ) Then I created the following measure that retrieves the last purchase date prior to each sale row... Last purchase date before respective sale = VAR Ticker_Ref = MAX( Tbl[Ticker] ) VAR Date_Ref = MAX( Tbl[Date] ) RETURN MAXX( FILTER( ALLSELECTED( Tbl ), [Ticker] = Ticker_Ref && [Date] <= Date_Ref && [Purchases] <> BLANK() ), [Date] ) ... which works just fine when dropped to a table visual: I tried to produce those same results above as a calculated column but I keep getting wrong results. How can I get that to work? Since I'm a DAX newbie, I still have a hard time reproducing a measure code as a calculated column or vice-versa... The closest I got was this... Last purchase date before respective sale = VAR Date_Ref = MAX( Tbl[Date] ) RETURN CALCULATE( MAX( Tbl[Date] ), ALLEXCEPT( Tbl, Tbl[Ticker] ), Tbl[Date] <= Date_Ref, Tbl[Purchases] <> BLANK() ) ... but it returns wrong results (max purchase row date per group of tickers, instead of max purchase row date within each group of tickers prior to or up to each respective sale row date): And before one suggests I should think long and hard before resorting to calculated columns as opposed to measures: I've already done such philosophical conundrum and realized that for this particular application, which is producing that piece of calculation, and others that follow, in order to reach a particular (and very complex) calculation goal, I have no other choice but go with calculated columns.
