Forum Discussion

Adrian_Celis's avatar
Adrian_Celis
Regular Visitor
2 years ago
Solved

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 ...
Tutorial Requests
  • CoreyP's avatar
    CoreyP
    2 years ago

    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!

CoreyP's avatar
CoreyP
Solution Sage
2 years ago

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's avatar
Adrian_Celis
Regular Visitor
2 years ago

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