How to get the Column Level Total for Actuals
I have a matrix visual and I want to show Column Level Total for the column named ACTUALS (see snip below). I tried to use the property of the matrix visual to show the column level total. However, with this approach all columns toals gets displayed namely (Planned, Actual, performance). I just want to show the Total for Actual How can I achieve this using DAX or any other technique. Note: I dont want to show Planned and Performance Total and this is how my columns have been placed on the matrix visual
max value for 2 categories
Hi All, i am having hard time finding the max value product. below is my table of data. for argentina 1336 is the max sold value and peripherals is the max sold item. i am able to get the max sold value with this measure Maxvalue = MAXX( KEEPFILTERS(VALUES(Sales[Product])), CALCULATE(SUM('Sales'[AMOUNT_SOLD])) ) but i am unable to get the max sold item name which is peripherals i have tried many many dax functions but i am not able to get that. kindly help. my ultimate result should be for each country one record with max sold item and its value. amitchandak Anonymous dax Pragati11 superDAX EDWSameperiodlastyear is giving me the total sales of last year instead of for the given time period
Hello Community, Hope your all well. So im currently working on income report for my company. I have calculated the total income and would now like to calculate the total income for the same period last year.Where my problem lies in is the fact that whenever I try to do the dax measure sameperiodlastyear it gives me the total income of last year instead of narrowing it down to the date filter I have selected. For reference my total income is calculated using the following measure: Deckunsbeitrag = SUMX( 'BES DE KOELN$VK-Umsatz Details', 'BES DE KOELN$VK-Umsatz Details'[DB (MW)] ) The sameperiodlastyear measure is the following: Deckunsbeitrag LY = CALCULATE( [Deckunsbeitrag], SAMEPERIODLASTYEAR(Datumstabelle[Date]) ) I then have a date filter which I put into a slicer on the dates ranging from the 01.01.21 to the 10.03.21.Like I previously explained the sameperiodlastyear measure takes the total income of the whole year instead of looking for the total income in the time period of 01.01.20 to the 10.03.20. Again for reference I do have a date table and use it so I'm very confused as to why I'm getting the total income of last year instead of getting it fo the given time period that I filter on on that page. Any help would be greatly appreciated and thank you all a lot .RE: SUMMARIZE AND SUM
Hi, Good day. I am making a report again and I do have a problem creating a 'pivot-table-look-a-like. Below is 2 tables i need to create using DAX. See the data and expected result. TABLE 1.: From This: Into this: (Summary) - all audited amount was placed to each status Table 2: FROM the outcome above - (Summary).. Into this: (Current Status Table) - this is to placed to proper status to match with the original amount. Please note that the total audited amount must not exceed the orig amount of the document code. Note 1: Please be guided that the outcome on the Table Scenario#2 are what I really want to achieve. Note 2: Each status prevails to other status if only has the same audited amount under the same doc code. a. For Reversal - prevails OVER all b. Closed Inact - prevails over In Progress, Closed-FYI Only, Issue Closed c. In Progress - prevails over FYI and Issue Closed d. Closed - FYI Only prevails over issue closed I hope you get it. And I hope you can help on this. Should you need more clarification please let me know. TIA and stay safe. 🙂Dynamic measure based on linear calculation/ statistics
Hello all We are trying to build dynamic yearly target for purchase managers by monthly percent economy from their purcheas deals $. there is 2 ways to do it: 1. By linear calculation of monthly purchase of entire months * target percent + average monthly purchase of entire month*target percent. i tried to calculate my purchase for all year with dax formula: MonthlyDynamicPurchase = if(month(MAX(dimDate[date]))<=month(today())-1,[TotalPurchase$],CALCULATE(SUMX(factOrdersPurchase,[TotalPurchase])/DISTINCTCOUNT(dimDate[month]),ALLEXCEPT(dimDate,dimDate[year]))) unfortunately it is'nt working , please see an attached pict. 2 second way is to calculate percentaje of monthly purchase from yearly , by last 5 years , calculate average of them and use it for making "prediction" of monthly purchase for new months i tried to use formulas above and it isn't working two because the slicers's effect ,i will realy appriciate any assistance / quide to make it working. the formulas i used: month_year_purchase = var _currentyear = YEAR( TODAY() ) RETURN CALCULATE( [TotalPurchase$], 'dimdate'[year] <> _currentyear ) ---------------------------------------- complete_year_purchase = var _currentyear = YEAR( TODAY()) RETURN SWITCH( TRUE(), SELECTEDVALUE(dimDate[year])= _currentyear, BLANK(), CALCULATE( [totalpurchase$], all(dimDate), VALUES(dimDate[year]), dimDate[year] <> _CurrentYear ) ---------------------------------------- Percent_of_yearly_Purchase = AVERAGEX ( VALUES ( dimDate[MonhYear]), CALCULATE ( DIVIDE ([month_year_purchase], [complete_year_purchase] ), ALLSELECTED ( dimDate[year ] ) ) ) )Performance issues with SUMX
Hi, everyone, I'm working on a dax measure performance, this measure contains as funtion SUMX (As SUMX is an iterator function, it is degrading the performance) and I woul like to use another alternative to fix the performance issue. the measure that I'm working on : Measure : SUMX(FILTER(VALUES(TABLE1[Column A]),[TAG]<>BLANK()),CALCULATE(DISTINCTCOUNT(TABLE1[Column A]))) Thank you for your response. My best regards.
Help Needed on Goals Reflecting in Matrix
I'm struggling with my goals showing on both the higher level and the lower level when my list is expanded. I have included details below and the measure I created in the hopes that the goal would only show on the higher level. I thought it would be an easy fix, but I'm stumped. You can see in the above that the higher level goal is listed as it should be, but when I expand the list to the lower level, the goal appears on each one. I have a relationship between the upper level and the goal, not the lower level and goals. The measure I created to try and address this is: Lead channel goal only = IF(NOT( ISFILTERED( 'leadsourcechannel'[amsen_name])), SUM ('Lead Goal'[Lead goal])) I'm hoping someone can help me, thanks so much! Kristi
