sumx

Hello experts, In my Power BI report I am trying to do a calculation on all rows in a table, then be able to correctly add them up in the subtotal lines by whatever grouping I am using. In my case, the final grouping I have in Matrix rows is 'Calendar'[EOM], which is not being used in the...

Hi, I'm having issues trying to get totals to appear for one of my measures. Below is the table I'm creating and I've highlighted the issue I'm having. Below that is the meaures I've created. Any help would be appreciated. Lost Check 1 = ([FX Renewable Income (CY)]=0 && [FX Renewable Premium...

I'm working with some data Where I have a branch, clients in a branch and products with that client. I've managed to get the values in a pivot from measures at product level provided I filter on a client. A soon as clear the client filter all the values become incorrect. Ideally I want to...

Hey all, I'm having some performance issues with Sumx that are causing very slow measure performance as was wondering if you smart people would know of a better way to accomplish these TestMeasure= 'Take the slicer selection date' VAR CalculationDate= [SelectedDate] 'Calculate USD values...

Hi, would appreciate any help on this, I want to get a weight on groups of rows in my Data using PoverPivot. So for example, the weight by Customer by Product in January. This sort of structure, but obviously a lots more rows ! Is it possible to sum the Amount column by row using variables...

I have a bunch of engineers who get assigned work for each day and I have come up with a scoring system for how well each is utilized on a particular day. What i would like the is overall average score for each day for all engineers, but getting something different that is an average of...

Hi I am yet again stumped by a DAX measure. I am trying to create a measure of forecast over leadtime i.e. The cumulative forecast over the leadtime of the Product (which varies by product). The calculation works at the Product level but fails during aggregation. I cannot resolve how I use...

Hi all I have a DAX challenge that i haven't been able to solve. I have 3 related tables; GL Account (columns: No, Name, type) GL Entries (Columns: Date, GL account no, Amount) Calender (columns: Date, year, financial year) I wish to make a measure (only 1) Which calculates sum(GL...

I am using SUMX to sum a calculation by Order Number in my table, and I want to add a new calculation that iterates over both Order Number and Item Number in my table, but having trouble doing so. My table looks something like <tbody> Date Order Number Item Number Other Columns... Sales...

I don't think dax can handle aggregated functions, but lets try. It stumped the powerbi community I've found dax is not super great at aggregate functions because you basically have to rebuild it for grand total purposes (and referencing them). Here's an example on grand totals I can't get...

I have a table in PowerPivot that is pulling a value from a related table using the related Function. I’m then trying to test if that value is 1 of 4 values, and if so, return a value from another related table, otherwise return a value from another column within the same table. For example, I...

Greetings all! I'm trying to create a report that will allow me to walk from 2016 sales to 2017 sales. I need to create a waterfall graph as well as a pivot that shows: 2016 Base add: New Sales Upsells Less: Lost Sales Downsells Total should equal 2017 Sales. I also need to show this from...

Hello there, I have a table in PowerPivot (let's just call it "table") and I need to create a calculated field that sums the value of field "currency" based on criteria within the table: sum(table[currency]) Where table[age] >9 and table[age] < value of table[age] for the current row and...

I need to calculate the variation of sales volume of the current month vs the same period of the previous year. At a product level the formula is working fine, but the subtotals and totals DO NOT ADD UP. I have tried many approaches and techniques available on the web, but I couldn't find...

Goodday, I want to use the SUMX function in combination with DISTINCT (PowerPivot + Excel 2010). For example I have this data: <tbody> Productgroup Product Sales Group Y product1 5 Group Y product2 4 Group X product1 5 Group X product3 3 </tbody> Products can be assigned to multiple...

Hello,I have a PowerPivot dataset for Football stats in which I have two measures created “RecYdsDomPct” and “RecTDDomPct” that I want to average together. The average of these should be 35.17%. However when I remove any of my other fields (day and game id) the percentages do not tie back to...

Hi, I am trying to calculate the duration between the earliest and last timestamps in a day for each person. Group Name Login Time Logout Time 1 Ben 2/5/2015 8:00 2/5/2015 9:00 1 Ben 2/5/2015 13:00 2/5/2015...

Hi All, I'm using excel 2010 with power pivot and like to create a new calculated column to allocate an amount to set of groups based on the no of distinct products in the group. I have uploaded the sample data here in the link below and if you can help me how I can get the same results in the...

Hi all, I've been struggling with this for a long while now and I hope someone can help: I have a PowerPivot table (I'm calling it 'Fruit Sales') and a pivot. I need to calculate a Cost per Fruit for 2012 weighted by Volume sold in 2013. I can create simple measures for this without...

I need a grand total that sums the column. I have an if statement in my measure and the Grand Total is showing up as blank rather than summing the column. I know that sumx will usually work but I'm not sure where to put it here. Here is the formula...