Forum Discussion

mallap849's avatar
mallap849
Helper I
3 years ago

Filter and summarize function

Hello everyone, I have a dataset named ‘Vehicle Parts’ from which I want to first filter the variable [ACCOUNT NAME] by ‘vehicle parts -warehouse’ and then I want to group by the variables – [TRANS_...
Need Help
mallap849's avatar
mallap849
Helper I
3 years ago

Sorry for some reason I cannot upload the .pbix file. But I hope screen shots below maske a better sense? Thank you, for the help!

 

Vehicle Parts - Table

 

From the above data I want to create a table that only selects – ACCOUNT_NAME  “Vehicle Parts- Warehouse” and that groups by variables – TRANS_DATE, ORG, ORG NAME and SUM – TRANS VALUE

 

DAX used to create a new table.

Expense =

VAR K =

  FILTER('Vehicle Parts', 'Vehicle Parts'[ACCOUNT_NAME] = "Vehicle Parts - Warehouse" )

  RETURN

  CALCULATETABLE(

      SUMMARIZE(

          'Vehicle Parts',

          'Vehicle Parts'[TRANS_DATE].[Date],'Vehicle Parts'[ORG],'Vehicle Parts'[ORG_NAME],

          "EXP" , SUM('Vehicle Parts'[TRANS VALUE])

      ),

      KEEPFILTERS( k )

  )

 

I want to create this table so that I can connect it with another table, below – Car Mileage Data, based two variables.

 

Car Mileage Data

 

Originally, I thought of create a table based on two tables above. Vehicle Parts data had Trans Value and Car Mileage Data has the METER_1_USAGE that I want in one table.

 

Regards,

Priyanka