Daily usage rollup

[Anonymous]

I am trying to create a report which shows the daily usage of our services.  our services report usage per region daily but i need to rollup the usage to only show 1 entry for each product per day.  I have looked at aggregate & group by functions but not sure what is the correct solution to use.  here is an example of my data and what i would like as a result.

Post Date	Product ID	Usage		Date	Product ID	Daily Usage
1-Jul-21	ABC	5		1-Jul-21	ABC	22
1-Jul-21	ABC	10		1-Jul-21	XYZ	15
1-Jul-21	ABC	7		2-Jul-21	ABC	31
1-Jul-21	XYZ	4		2-Jul-21	XYZ	16
1-Jul-21	XYZ	5		3-Jul-21	ABC	25
1-Jul-21	XYZ	6		3-Jul-21	XYZ	27
2-Jul-21	ABC	6
2-Jul-21	ABC	15
2-Jul-21	ABC	10
2-Jul-21	XYZ	6
2-Jul-21	XYZ	5
2-Jul-21	XYZ	5
3-Jul-21	ABC	10
3-Jul-21	ABC	10
3-Jul-21	ABC	5
3-Jul-21	XYZ	8
3-Jul-21	XYZ	9
3-Jul-21	XYZ	10

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

If you want to achieve this by creating a new column,create  new column use the below dax:

test1 = CALCULATE(SUM('Table'[Usage]),ALLEXCEPT('Table','Table'[Post Date],'Table'[Product ID]))

If you want to achieve through visual effects:(create table visualizaiton like below) :

And if you still want to create a new measure,use following daxs:

test2 = CALCULATE(SUM('Table'[Usage]),ALLEXCEPT('Table','Table'[Post Date],'Table'[Product ID]))

or

test3 = CALCULATE(SUM('Table'[Usage]),FILTER(ALL('Table'),'Table'[Post Date]=MAX('Table'[Post Date])&&'Table'[Product ID]=MAX('Table'[Product ID])))

or

test4 = SUM('Table'[Usage])

Wish it is helpful for you!

Best Regards

Lucien

[DataInsights]

@Anonymous,

Try this measure:

Total Usage = SUM ( Table1[Usage] )