Search code examples
powerbisumdax

How to consolidate the sales data using DAX

enter image description here

Hi everyone,

I have a data table named as sales in PBI as shown in the screenshot above. I want to create a matrix visualizer to consolidate all the sales by month. The expected output for the matrix will be something like this:

enter image description here

I'm having challenge to use DAX formula to consolidate the sales. Right now, I'm using SUM(sales[Sales]) to calculate, but what I got in the matrix for each rows is the total sales. I tried to used SUMX as well but it doesn't work either. Any help or advise will be greatly appreciated!

Solution

  • If you have it stored as a string SUM functions won't work - which is the only error I can think of with data as simple as that.

    You also don't need to make a function for something like this. Just make a new table with "Month & Year" and "Sales" it'll default to using SUM of sales if it's stored as a number or integer.