Run a query against a dataset in Power Query measure calculation in row context

[KasperJ90]

Hi,

I succesfully set up a flow for a table in my Power BI dataset. I am loading this created CSV file into my dataset as a way of saving historical data:

Now I want to do this for another table, but this tables includes measures. I need the result of the measure to be calculated for each row in the data I save similar to if it was a column.
This means the Total Sales Measure should only be calculated for each row. I do not understand how the calculation is being performed now, but the numbers I receive are way to high but still different from row to row in the CSV file I create.
See below a part of the code in the "run a query against a dataset" section in Power Automate. 

DEFINE
VAR _Table =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Year] > 2020
)
,"Customer", 'Table'[Customer]  ''<<< COLUMN
,"Product", 'Table'[Product]  ''<<< COLUMN
,"Total Sales", '[Total Sales Measure]  ''<<< MEASURE
)
EVALUATE
_Table

Current result example:
Customer	Product	Total Sales Measure
A	X	67.000.000
B	Y	43.000.000
Requested Result example:
Customer	Product	Total Sales Measure
A	X	3.500
B	Y	1.500

I can not add the Total Sales measure as a calculated column etc. but need the result for each line so I can compare changes over time. 
Can you help explaining how the measures are being calculated and more importantly how to get the correct results?

[Anonymous]

Hi @KasperJ90 ，

Please try:

DEFINE
VAR _Table =
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Year] > 2020
),
"Customer", 'Table'[Customer],
"Product", 'Table'[Product],
"Total Sales", CALCULATE('[Total Sales Measure]', ALLEXCEPT('Table', 'Table'[Customer], 'Table'[Product]))
)
EVALUATE
_Table

The function removes all filters from the 'Table' except for the filters on 'Table'[Customer] and 'Table'[Product], ensuring that the measure is calculated for each row considering the specific Customer and Product. 

Please try this modified DAX query and see if it provides the correct results for your scenario.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum