dax measure

  1. J

    Row Total Incorrect Using SumX

    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...
  2. R

    Only show Subtotal and not the detail within rows (Powerpivot)

    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...
  3. S

    Lookup from different table in Dax Measures

    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>...
  4. T

    DAX RankX Troubleshooting with PowerPivot

    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...
  5. M

    DAX - Return multiple column rows from a single column row based on criteria

    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...
  6. O

    Rolling 5years - DAX measure

    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 =...
  7. B

    Dealing with (multiple) many2many relationships in Powerpivot

    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...
  8. N

    SUMIFS not working properly

    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...
  9. K

    Calculate discount based on dates variable in PowerPivot

    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...
  10. J

    DAX Linear Interpolation

    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"...
  11. N

    Events in Progress - Why do none of the online solutions work?

    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 (...
  12. L

    PowerPivot many-to-many relationships

    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...
  13. L

    PowerPivot multi-criteria measure

    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...
  14. B

    Creating a measure to calculate percentage of total that updates with slicer selection

    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...
  15. S

    Reusable DAX measure with different slicers

    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...
  16. E

    100% ALLSELECTED or Other Filter

    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...
  17. A

    DAX measure using text column

    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...
  18. L

    PowerPivot Linking two fact tables

    Hello, firstly, Hello to all - I am new to the fourm. I hope someone can help me, I have a model that has two tables for Forecast (Labour and Non-Labour) The issue I have is that I would like to be able to view the data in one field in the pivot table. at the moment I have them as. e.g...
  19. K

    Workaround for TRUE/FALSE Expression error

    Hi all! I'm desperately trying to get PowerPivot to count the number of 'L's in a column. L itself is a measure, which returns 'L' for stores which have not bought in over a year (and are therefore Lost) and blank for all other cases. When I try the following function...
  20. Z

    PowerPivot measure - Cacluate() using filter and "OR" syntax

    I am using the below measure in PowerPivot (Excel 2013): =CALCULATE(COUNTA(ShoppingCart[Fruit]), ShoppingCart[Fruit]="Apple") This will give me the the number of records in the "Fruit" column that are equal to "Apple". I'd like to include "Oranges" in my filter, so that I can have a count of...

Some videos you may like

This Week's Hot Topics

Top