Forum Discussion
Aggregating using DAX
Hi Everyone,
I'm a little new to Power BI. I have this table called 'PurchaseOrders' with columns 'ResellerID', 'Date' and 'PO Total VAT Ex'.
What I want to do is get:
1. the sum of all 'PO Total VAT Ex' for each ResellerID and each YEAR.
2. the count of items for each ResellerID and each YEAR.
My first thought of how to do this is create another query and then use Group By on on Power Query. Which works but then I have to create 2 new queies. I am thinking this is not the best way to do it.
Now I am thinking of creating a measure on the table instead. But I am not yet familiar with DAX. So
I ask if the 'measure' approach is correct and how do I write the DAX formula for each one?
Thank you very much!
Adrian
Create a date dimension table and establish a relationship to your date column. Make your reseller ID data type of text. Create two measures:
Total VAT = SUM( Vat Column )
Item Count = COUNT( Item Column ) or if you want unique item count, DISTINCTCOUNT
Then on your report canvas, drag the Year column from your date table, the Reseller ID column, and your two measures into the visual of your choice.
*There are some awesome Power BI 101 videos online which will give you the foundation you need. Good luck!
4 Replies
Hello Adrian_Celis ,
check how to use sum and count using dax from the following links
https://learn.microsoft.com/en-us/dax/sum-function-daxhttps://learn.microsoft.com/en-us/dax/count-function-dax
and in your case here
Sum of total PO = sum ( 'YourTableName' [PO Total VAT Ex])
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Follow me on Linkedin
Vote for my Community Mobile App Idea 💡Thank you for your reply. So I assume you mean the measure approach is the right way to do it.
I have already came accross the sum and count functions but as asked above, how do you aggregate with these conditions:1. the sum of all 'PO Total VAT Ex' for each ResellerID and each YEAR.
2. the count of items for each ResellerID and each YEAR.
Thank you
Adrian
Create a date dimension table and establish a relationship to your date column. Make your reseller ID data type of text. Create two measures:
Total VAT = SUM( Vat Column )
Item Count = COUNT( Item Column ) or if you want unique item count, DISTINCTCOUNT
Then on your report canvas, drag the Year column from your date table, the Reseller ID column, and your two measures into the visual of your choice.
*There are some awesome Power BI 101 videos online which will give you the foundation you need. Good luck!
