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!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors