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
Anonymous
Not applicable

Divide by Total

Hi community, 

 

I'm facing with an issue that soundy very simple: 

 

I would like to divide my cost positions by total of my revenue. 

image.png

This formula 

Delta_Cost_Rev =  DIVIDE([Cost pos]; [Revenue])
 
Doesn't work.

image.png

 

Am I able to save the total value of my revenue (105.106,50) in a kind of variable to reuse it and divide each other values without any connections of the tables in my data model? 

 

Any ideas? 

 

Thank you in advance!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The issue is actually VERY visible in your matrix.

On the first row, REVENUE is avaiable, but not COST.
On the 2nd to 4th rows, COST is available. while REVENUE not.

Therefore your DIVIDE will always have one less value.

What you need to do is to make REVENUE available on the 2nd to 4th rows.

I can't see the model but looks like the "rows" of your tables are "Deparment"? Anyway, let's imagine that your table called "Table" and the rows you're showing are Departments column.

Delta_Cost_Rev=DIVIDE([Cost pos];Calculate([Revenue];REMOVEFILTER(Table[Department]));0)

(if you don't have the very latest version of PowerBI replace REMOVEFILTER with ALL)

What you have to do is to allow Dax to exit from the filter context of those 2nd to 4th rows and see the value of revenues in the 1st row. And you do that by removing the filter of the department.

Last thing: when you use DIVIDE, always put the default value (in this case 0) as the last parameter.

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Try something like 

Measure = 
DIVIDE(
    [Cost pos]; 
    CALCULATE( 
        [Revenue];
        ALLSELECTED()
    )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

I'm not sure ALLSELECT would work here. ALLSELECTED removes the filter context of rows and columns while keeping other filters outside of the table. But in this case there are years in the columns so it would remove the years too.

Anonymous
Not applicable

The issue is actually VERY visible in your matrix.

On the first row, REVENUE is avaiable, but not COST.
On the 2nd to 4th rows, COST is available. while REVENUE not.

Therefore your DIVIDE will always have one less value.

What you need to do is to make REVENUE available on the 2nd to 4th rows.

I can't see the model but looks like the "rows" of your tables are "Deparment"? Anyway, let's imagine that your table called "Table" and the rows you're showing are Departments column.

Delta_Cost_Rev=DIVIDE([Cost pos];Calculate([Revenue];REMOVEFILTER(Table[Department]));0)

(if you don't have the very latest version of PowerBI replace REMOVEFILTER with ALL)

What you have to do is to allow Dax to exit from the filter context of those 2nd to 4th rows and see the value of revenues in the 1st row. And you do that by removing the filter of the department.

Last thing: when you use DIVIDE, always put the default value (in this case 0) as the last parameter.

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.