Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshua1990
Post Prodigy
Post Prodigy

DIVIDE for just for specific codes

I have a dimensional table that shows me for each product the product group:

ProductProduct Group
A1A
A2A
B1B

 

Then I have a sales table, that shows me the sales per Product and the last table with the budget per Product.

Now I would like to divide each sale on the product level versus the budget, but somehow the aggregation on the department level doesn't work.

DepartmentProductSalesBudgetDIVIDE
AA1500500100%
AA2500500100%
AA3500 0%
BB125030083%
 Total17501300135%
     
 Right1250130096%

 

There should be 96% as a result shown but I got 135%.

Do I need an iterative function here? Like a SUMX?

1 REPLY 1
amitchandak
Super User
Super User

@joshua1990 , Use this for sales

sumx(summarize(table, Table[Department], Table[Product], "_1", sum(Table[sales]), "_2", sum(Table[budget])), if(isblank([_2]), Blank(), [_1]))

 

or use this using isinscope or hasonevalue in GT

 

https://xxlbi.com/blog/new-dax-function-isinscope/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors