Forum Discussion
Data trasnform and calculation
Hi Experts,
data set is like below.
and i pivot this tables in power query as below. How could i get the amount value with Volumn*Price(with every records multiple)? Please help.
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
If fixing the table structure is not allowed, the below may be the one of many ways to solve this.
Volume X Price: = SUMX ( VALUES ( 'Calendar'[Year-Month sort] ), CALCULATE ( SUM ( Data[Value] ), Data[KPI] = "Volume" ) * CALCULATE ( SUM ( Data[Value] ), Data[KPI] = "Price" ) )
2 Replies
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
If fixing the table structure is not allowed, the below may be the one of many ways to solve this.
Volume X Price: = SUMX ( VALUES ( 'Calendar'[Year-Month sort] ), CALCULATE ( SUM ( Data[Value] ), Data[KPI] = "Volume" ) * CALCULATE ( SUM ( Data[Value] ), Data[KPI] = "Price" ) )