Search found 1036 matches

by MattAllington
Sun Apr 14, 2019 9:52 am
Forum: PowerPivot/DAX
Topic: Average of Totals
Replies: 3
Views: 122

Re: Average of Totals

How about this.

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

=
AVERAGEX (
    VALUES ( dApplications[SLR] ),
    SUMX ( VALUES ( dApplications[APPID] ), [AverageWgt%] )
)


Read my article about SUM vs SUMX It also applies to AVERAGEX
by MattAllington
Sun Apr 14, 2019 9:42 am
Forum: PowerPivot/DAX
Topic: YTD-MTD does not work for Column Total
Replies: 1
Views: 72

Re: YTD-MTD does not work for Column Total

How about this

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

=
SUMX (
    VALUES ( Calendar[Period] ),
    IF (
        [Total Amount YTD] > 0,
        [Total Amount YTD] - [Total Amount Prior Month YTD]
    )
)


Read my article about SUM vs SUMX
by MattAllington
Sat Apr 06, 2019 4:36 am
Forum: PowerPivot/DAX
Topic: Date Relationship between two tables
Replies: 6
Views: 216

Re: Date Relationship between two tables

Nope, it's not the RETURN. Try the same formula as a column, but remove the MAX = VAR SelectedDate = 'Calendar'[datetype] RETURN CALCULATE ( MAX ( Data[Texttype] ), FILTER ( Data, SelectedDate >= Data[valid from] && SelectedDate < Data[valid until] ) )
by MattAllington
Sat Apr 06, 2019 4:22 am
Forum: PowerPivot/DAX
Topic: Data Model/File crushes without a reason
Replies: 3
Views: 94

Re: Data Model/File crushes without a reason

Yeah, looks pretty straight forward. Power Pivot for Excel has not been stable over the years. The version in Excel 2016 is supposed to be pretty stable now, but I don't use it a lot myself. Sorry, I don't know how to fix this.
by MattAllington
Fri Apr 05, 2019 8:28 pm
Forum: PowerPivot/DAX
Topic: Date Relationship between two tables
Replies: 6
Views: 216

Re: Date Relationship between two tables

I think the same formula will work as a calc column
by MattAllington
Fri Apr 05, 2019 4:00 pm
Forum: PowerPivot/DAX
Topic: Calculated Column
Replies: 4
Views: 86

Re: Calculated Column

please post a sample power pivot workbook
by MattAllington
Fri Apr 05, 2019 3:59 pm
Forum: PowerPivot/DAX
Topic: Date Relationship between two tables
Replies: 6
Views: 216

Re: Date Relationship between two tables

you don't need a calculated column, unless you want to use this in a slicer or similar. Here is a measure = VAR SelectedDate = MAX ( 'Calendar'[datetype] ) RETURN CALCULATE ( MAX ( Data[Texttype] ), FILTER ( Data, SelectedDate >= Data[valid from] && SelectedDate < Data[valid until] ) ) examp...
by MattAllington
Fri Apr 05, 2019 6:15 am
Forum: PowerPivot/DAX
Topic: Trying to calculate averages per month and accumulated with DAX [Solved]
Replies: 1
Views: 80

Re: Trying to calculate averages per month and accumulated with DAX [Solved]

I suggest you do the following so others can understand what you are trying to do 1. rename your tables to something more meaningful other than dTable1 2. rename your columns to what is in the column 3. Post a sample workbook containing you measures in pivot table and the actual numbers you expect t...
by MattAllington
Fri Apr 05, 2019 6:13 am
Forum: PowerPivot/DAX
Topic: Data Model/File crushes without a reason
Replies: 3
Views: 94

Re: Data Model/File crushes without a reason

There are lots of things here. Using different versions of Excel is not ideal as Power Pivot is different. But the design of your model is also relevant. Personally I would never load data to Power Pivot from within the workbook. It is best to put the source in one workbook and then load the data in...
by MattAllington
Thu Mar 28, 2019 5:26 pm
Forum: Power Query
Topic: Extract web page data [Solved]
Replies: 2
Views: 191

Re: Extract web page data [Solved]

Have you tried the new query by example feature in power behind desktop? I’m not sure if it is still in preview or whether it is generally available now, but check the preview features if you can’t see it

Go to advanced search

cron