Aggregating using DAX

[Adrian_Celis]

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

[Idrissshatila]

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-dax

https://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 💡

[Adrian_Celis]

Hi @Idrissshatila 

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

[CoreyP · Accepted solution]

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!

[Adrian_Celis]

Hi @CoreyP 
Thank you! It seems to work and simpler than I thought.

I was overthinking that the formula should be something like Sum of PO Total VAT Ex filtered by current item's year and reseller ID. But you just have to drag them in the report canvas.
Thanks for the help.

Regards,

Adrian