Move Calculated Column Measure to Power Query

[Anonymous]

Hi Experts

I would like to re do my calculated column measure in Sales_DATA_Master table. within Power Query as a Custom Column as its blowing up my PBIX file size from 17MB to 82MB when i have 11 calculated columns in my Sales FACT Table.

Measure i would like to move

4.Purch_Direct = Sales_Data_Master[Gross_Cost_USD] / CALCULATE(sum('Sales_Data_Master'[Gross_Cost_USD]),ALLEXCEPT('Sales_Data_Master','Sales_Data_Master'[Channel],'Sales_Data_Master'[Region],'Sales_Data_Master'[Years_In_Date],'Sales_Data_Master'[Reporting_Region_l2],'Sales_Data_Master'[Month]))

Sample Date

https://www.dropbox.com/s/9fyj1bg94pnxyd7/Test_.pbix?dl=0 

[amitchandak]

@Anonymous , Seem like you want to create % of subtotal/ total

You have to create a summarize table in power query with the required total and merge

refer: https://exceloffthegrid.com/power-query-group-by/

then merge https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

or refer

https://social.technet.microsoft.com/Forums/en-US/1891afde-7392-4d27-b920-dfd579837dc8/how-to-add-subtotal-in-a-row-in-power-query?forum=powerquery

[Anonymous]

Hi Amit. I am not working out the % of Sub-Total over Total. Its the Working out the % of each line item over total based on Channel, Region, and other criteria....