Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.