cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mgtaylor3
Helper I
Helper I

M Query Equivalent to DAX Calculated Columns

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.
1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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

View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

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

View solution in original post

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors