Search found 133 matches

by PhilC
Tue Jun 20, 2017 9:17 am
Forum: PowerPivot/DAX
Topic: Is it possible to group dates into "Month - Year" format, without creating two separate groupings (by month and by year)
Replies: 6
Views: 169

Re: Is it possible to group dates into "Month - Year" format, without creating two separate groupings (by month and by y

Thanks for posting a workbook. Firstly, you are using a Pivot Table, rather than a PowerPivot data model, so the responses here would not have anticipated that as this is a PowerPivot forum. Attached is an approach that seems to work, using an additional field of YY-MM (not a data formatted, but eff...
by PhilC
Fri Jun 16, 2017 11:01 am
Forum: PowerPivot/DAX
Topic: Is it possible to group dates into "Month - Year" format, without creating two separate groupings (by month and by year)
Replies: 6
Views: 169

Re: Is it possible to group dates into "Month - Year" format, without creating two separate groupings (by month and by y

It would be interesting to see an actual file, as it seems what you term clumsy it perhaps what many of use would ordinarily use, ie nested fields (what you term hierarchy). Potential options: 1. Not sure if this will work, but if you have a calendar table, order the Month field you are displaying b...
by PhilC
Thu Jun 15, 2017 1:59 pm
Forum: Power BI Desktop/Service
Topic: Running Total In a Power BI table
Replies: 2
Views: 101

Re: Running Total In a Power BI table

Based on the way you have described Show Values As you are not using PowerPivot, but normal Pivot table functionality. If you post an example file here people might be able to assist to develop the measures you need. Pretty sure you will also need a Calendar table to enable the running total, so per...
by PhilC
Wed Jun 07, 2017 3:45 pm
Forum: Power Query
Topic: Some individual cells in the final output table need to be modified and survive through refresh
Replies: 7
Views: 177

Re: Some individual cells in the final output table need to be modified and survive through refresh

Hi omo, Google "Power Query incremental refresh" and you might find some useful resources to fit your specific situation. Matt has one approach but could not find the link with a quick scan. Some aspects to consider: - can the needed edits to the data be done in the original tables, so whe...
by PhilC
Wed Jun 07, 2017 8:45 am
Forum: PowerPivot/DAX
Topic: sumif equivelant
Replies: 1
Views: 82

Re: sumif equivelant

Hi, Providing a file with tables, columns and data would help with giving assistance, but the following may get you going in the right direction. Assumptions: Tables Names: Balances, Leave_Taken In Leave_Taken table Leave Taken:= SUM(Leave_Taken[Hours]) Annual Leave Taken:= CALCULATE([Leave Taken],L...
by PhilC
Fri May 26, 2017 9:33 am
Forum: PowerPivot/DAX
Topic: Lost Customers
Replies: 6
Views: 257

Re: Lost Customers

Hi Jes, Thanks for that, at a quick glance it looks like it is doing what I am after, but will do some more testing. Thanks for taking the time. Will take me a while to understand how that expression is working, as SUMMARIZE seems to be quite a complex function. Are you able to explain the FILTER wh...
by PhilC
Thu May 25, 2017 11:21 am
Forum: PowerPivot/DAX
Topic: Lost Customers
Replies: 6
Views: 257

Re: Lost Customers

Bump, any assistance with this would be appreciated.

Cheers
Phil
by PhilC
Mon May 22, 2017 9:10 am
Forum: PowerPivot/DAX
Topic: Avg invoice amount
Replies: 4
Views: 181

Re: Avg invoice amount

Hey Matt,

I think that gives the wrong average. As you have multiple rows for an invoice, you need to total the invoice first, then get the average of the invoices then.

Cheers
Phil
by PhilC
Wed May 17, 2017 3:25 pm
Forum: Power Query
Topic: Inner Join question
Replies: 1
Views: 165

Re: Inner Join question

Came across the following post that might be useful but cannot get it to work. https://community.powerbi.com/t5/Community-Blog/Conditional-Code-Branching-in-Power-BI-Query-if-then-else-gt/ba-p/39998 M code is let Source = Data, #"Merged Queries" = Table.NestedJoin(Source,{"Budget Cent...
by PhilC
Wed May 17, 2017 1:42 pm
Forum: Power Query
Topic: Inner Join question
Replies: 1
Views: 165

Inner Join question

Hi, Am looking for a solution that would allow me to use a manually maintained table as a filter for a dataset, but also allow no filtering to occur. I set up a one column table with values for the Budget Centres I wanted to be included in the data loaded, and used an Inner Join. This works if I onl...

Go to advanced search