M Query Equivalent to DAX Calculated Columns

[mgtaylor3]

Good Morning all,

Ok my query is this, I have a General Ledger Table, where i need to Calculate the Monthy Revenue, for Each 'Territory' based on  a specific Reporting Segment, to Gauge the % of Total Revenue for that Month. ( For that Reporting Segment and Supsequent Territory)

Using that %  to calculate, from a Total COGS for the month, the  proportional amount of that COGS total, using the % that has been calculated. 

This is to then be part of the FACT TAble, not a DAX Calc..

DAX Calc's used are:

Revenuetotal =

CALCULATE(

SUM([Amount Budget]),

FILTER(

'Fact: AGG_GENERALLEDGER (2)',

'Fact: AGG_GENERALLEDGER (2)'[FIN_YEAR] = EARLIER('Fact: AGG_GENERALLEDGER (2)'[FIN_YEAR]) &&

'Fact: AGG_GENERALLEDGER (2)'[FIN_MONTH_NO] = EARLIER('Fact: AGG_GENERALLEDGER (2)'[FIN_MONTH_NO]) &&

'Fact: AGG_GENERALLEDGER (2)'[Account Type]="Revenue"&&

'Fact: AGG_GENERALLEDGER (2)'[Reporting Segment] = EARLIER('Fact: AGG_GENERALLEDGER (2)'[Reporting Segment])

)

)

TerritoryRevenue =

CALCULATE(

SUM([Amount Budget]),

FILTER(

'Fact: AGG_GENERALLEDGER (2)',

'Fact: AGG_GENERALLEDGER (2)'[FIN_MONTH_NO] = EARLIER('Fact: AGG_GENERALLEDGER (2)'[FIN_MONTH_NO]) &&

'Fact: AGG_GENERALLEDGER (2)'[Territory] = EARLIER('Fact: AGG_GENERALLEDGER (2)'[Territory]) &&

'Fact: AGG_GENERALLEDGER (2)'[Account Type]="Revenue"&&

'Fact: AGG_GENERALLEDGER (2)'[Reporting Segment] = EARLIER('Fact: AGG_GENERALLEDGER (2)'[Reporting Segment])

)

)

TerrPerc =

('Fact: AGG_GENERALLEDGER (2)'[TerritoryRevenue]/'Fact: AGG_GENERALLEDGER (2)'[Revenuetotal])

COGStotal =

CALCULATE(

SUM([Amount Budget]),

FILTER(

'Fact: AGG_GENERALLEDGER (2)',

'Fact: AGG_GENERALLEDGER (2)'[FIN_MONTH_NO] = EARLIER('Fact: AGG_GENERALLEDGER (2)'[FIN_MONTH_NO]) &&

'Fact: AGG_GENERALLEDGER (2)'[Account Type]="COGS"&&

'Fact: AGG_GENERALLEDGER (2)'[Reporting Segment] = ('Fact: AGG_GENERALLEDGER (2)'[Reporting Segment])

) )

CogsPerc =

'Fact: AGG_GENERALLEDGER (2)'[COGStotal]*'Fact: AGG_GENERALLEDGER (2)'[TerrPerc]

This then does give me almost what I'm after.

Thing is I need the end results to be merged/appended to the Fact table as a new table.

I can't see how to do that via the method I've created so barring doing this in the original SQL Query, Can this be done via M Query and if so any suggestions?

Many thanks in advance. Mike.

[BA_Pete · Accepted solution]

Hi @mgtaylor3 ,

Power Query can do all of this if you push it hard enough, but the query would be disproportionally complicated and would take a week to refresh.

Power Query/M is just not designed for what you are wanting it to do here, DAX is.

Your original path appears to be the correct one from the limited info I have. The only comment I would make on what you have done so far would be to say that DAX calculated columns should only be used as a very last resort. They have to be materialised in-memory at runtime so can cause significant performance degradation.

The calculated columns you described should be recreated as measures.

Pete

[mgtaylor3]

Hi @BA_Pete , many thanks for that, your anser was pretty much what i was expecting. Totally agree in creating measures rather than the calc columns, my viewpoint was to create a table of the proportioned data to show an end result. I think in the end calculated measures may be my answer.

Regards Mike.