dax measure

Hi, Been fighting with chatgpt whole day but it seems impossible. So I have a dataset with orders. Made a report with a slicer to filter the order dates, this is made dynamic. I have a measure which works for this dynamic filter to calculate the SUM of orders: Dynamic measure = CALCULATE(...

I'm trying to create a visualisation that goes back 12 months from the selected date in a slicer. Using this video - I have this code: TRI P12M = VAR ReferenceDate = MAX ( 'Date'[Date] ) VAR PreviousDates = DATESINPERIOD ( 'Previous Date'[Date], ReferenceDate...

I am NEW to Power BI and DAX. I am trying to write a measure and place it in a matrix that filters a list of customers based upon selected dates within a slicer (a customer must be a customer in ALL dates selected). For example, if I have five customers, and I select the dates 01/01/2020...

I have a simple table and am using sumx, but the row total is incorrect. The measure is: =sumx(daily,[Gallons]*[NACVal]/[Acres])/[rows] The output looks like this: <colgroup><col><col span="2"><col><col span="5"><col></colgroup><tbody> <colgroup><col span="2"><col><col...

Hi, Is it possible to create a measure that will only be used as a sub-total. I have a field in my table called "Present", the data for this is allocated to the top row for the daily records.When this is pivoted all I want to show is the sub-total and not the detail for that particular row...

Hello Everyone, I am looking for a dax function measure for powerpivot report which would return the value based on a lookup value from different table. For Example,I have 2 tables as follows <tbody> City Name State_Code Mumbai MH Pune MH Chennai TM Noida UP </tbody> <tbody>...

Hello, I am having issues using the RANKX function in my PowerPivot data model. I am trying to rank two different metrics by region, territory: sales and growth from last year. The sales rank works perfectly, but the growth rank seems to skip a rank when growth turns from positive to negative...

I am creating a stores report in Excel 2016 using PowerPivot I have been asked to include measures relating to any stores that are within 10% of the size (m3) of a given store, i.e. return a list of stores that are within that size threshold. I do not need to query against more than one store...

I have a table (matrix) with... RegistrationYear (2001, 2002...2025) Brand (MB, VW, ...) NoOfCarsSold (quantity) How can I calculate the 'Rolling 5years park' by Brand and Year ? Rolling 5years park: Rolling5-Yr2005 = Sum(2001, 2002, 2003, 2004, 2005) by brand Rolling5-Yr2006 =...

I have a question about how to deal with multiple many2many relationships in Powerpivot. Here is my scenario: I have a bunch of products, where each product is assigned to at least one but potentially more categories, so for example my first table is Product Category P1 C1 P1 C2...

Hi All,I have a big file with ratio and amounts in column. From this I want to calculate the weighted average of a ratio. However, all the ratios and amounts are in the same column. They are for several years and for each year several scenarios and for each scenario several balance items for...

Hi! I have two tables in my PowerPivot (see example below). The first one (Table A) shows different packages at different hotels and how much they cost. The second table (Table B) shows how much discount we can get from the different hotels. The discount is based on when the guest are staying...

Hello, One table is a "lookup" table that contains probabilities and scores. The other is a "fact" table with scores and a column for probabilities. I am looking for a DAX measure that will return the correct probability by linearly interpolating the score in the "fact" table to the "lookup"...

I am trying to find a count and dollar amount of events in progress when a user selects a single week. I want all events and dollars with a start date less than the max date of week selected and end date after the max date selected. Here is my formula for the job count: Calculate ( CountRows (...

Good day My data lies in Table1 and Table2 as follow. "Users" are assigned several "Roles" and each "Role" give authorization to several "TCodes". This is a many-to-many relationship, which cannot directly be handled via PowerPivot relations. Which Calculated Measure (DAX) will allow me see...

Hi Everybody, Firstly, let me preface this enquiry by saying that while I'm proficient using Excel I'm relatively new to PowerPivot/DAX. I would like to set up a persistent model that can automatically calculate the date at which equipment is to be next maintained. Note I have structured this...

I bet there is a simple formula for this, but I'm new to power pivot and DAX and I can't get this to work how I would like. I created a power pivot table with the Row Label "Activity" and the values "Activity Total" and "Activity Count". Activity total is the dollars associated with the Activity...

Hello I am having trouble writing a measure that works correctly for different slicers. I have 2 tables: Table 1 - store and area; Table 2 - program, store, # events, # calls (see below as could not attach a file). Company performance = # events (stores in East and West) plus # calls (stores in...

We have a need to analyze sales by sales type , region and period (Month/Quarter/Year). When choosing the values from the above filters the 100% reference should be by the chosen filters and not by the whole derived data. For example, If I choose to filter and present Sales order of type...

Hello I am using power pivto in Excel 2010 in order to create some reports, the problem is that in my reports i need not only numeric columns but text. I know DAX measures can return text, but in this case the problem is that the text column should be used as input for the measure. My data are...