Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculate DAX Measure instead in Power Query M

Hi all,

 

I have a question regarding building a measure instead in DAX I want to create it in Power Query M and I struggle a bit.

Please let me know how this could be feasable. 

The reason why I am doing this is because I want to add the result of the measures to my raw data and display it together in a matrix visual. If you know a different approach for that let me know. 🙂

 

So i have the two following measure created

1) Calculates the percentage of goods that were returned by the customers.

 

Returned Goods Percentage  =
VAR Number_of_returned_goods=
CALCULATE ( COUNTROWS ('Sales'), ( 'Sales'[Return] ) = "Yes" )
VAR Total_Sales =
CALCULATE ( COUNTROWS ('Sales') )
RETURN
DIVIDE ( Number_of_returned_goods, Total_Sales )

 

2) Here I want to calculate my turnover for a certain filter compared to the total turnover

 

Turnover Percentage =
VAR Turnover_Group1=
CALCULATE ( SUM('Financial Data'[Sum]), FILTER('Financial Data', 'Financial Data'[Group] <> "Group 1" && 'Financial Data'[Turnover?] = "Turnover"))+0
VAR Total Turnover =
CALCULATE ( SUM('Financial Data'[Sum]), FILTER('Financial Data','Financial Data'[Turnover?] = "Turnover"))+0
RETURN
DIVIDE ( Turnover_Group1, Total Turnover)

 

Cheers

Maronjo

Need Help

3 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    I'm not very clear about your thoughts. The measure you created is not suitable to be placed in a column, so what result do you want to get in PQ?

     

    Best Regards

    Janey Guo

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi v-janeyg-msft ,

       

      thank you for your reply.

       

      Maybe it makes more sense to explain my scenario more detailed. I want to create a KPI report, where a smaller number of KPIs are calculated by myself like the measures I posted here. The other KPIs can not be calculated by myself instead I have another input file where the numbers are included. My idea is to show all KPIs in a matrix, thats why I kind of want to calculate the measures via pq to have the result in a table and join it with my input table. Another option was I calculate the measures and then I have to manually take the number and add it into the file which includes all the other KPIs, but I want to avoid that.

       

      Do you have any idea how that could work?

       

      Best regards

      Maronjo

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        5 years ago

        Hi, Anonymous 

         

        There is no measure in pq, and measure is a value that does not exist in the data. You can try to change the measure to calculated column and use it. It need to be modified according to the data.

        If you don't solve it after trying, you need to share some sample data and your desired result, and feel free to ask me.

         

        Best Regards

        Janey Guo