Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 @Anonymous ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

Hi @Anonymous ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.