topic Re: Divide per Category, combining Products in DAX Commands and Tips

Divide per Category, combining Products

Anonymous — Mon, 24 Jun 2024 13:42:01 GMT

Hi all,

I'm currently facing an issue with a divide measure in Power BI.

Here's a simplified version of my data:

I want to create a measure for the last column that calculates Measure 1 divided by Measure 2. The desired result should look like this:

The challenge is that I need this measure without grouping the products, as new products are frequently added. I've attempted to use the REMOVEFILTERS and ALL functions, and I have managed to create two separate measures successfully, but combining measures ends in an incorrect total value.

Could anyone suggest an approach to solve this using DAX?

Thank you!

Re: Divide per Category, combining Products

JohnAPD — Mon, 24 Jun 2024 14:22:01 GMT

Hi Anonymous can you clarify the value of Product D in Category C Changed from blank to 8 is this intentional?

Re: Divide per Category, combining Products

JohnAPD — Mon, 24 Jun 2024 14:36:40 GMT

If this is the case then you could create a filter measure to use in the filter pane:

BooleanInclude = VAR _Msr1 = CALCULATE ( SUM ( 'Table'[Measure1] ) ) VAR _Msr2 = CALCULATE ( SUM ( 'Table'[Measure2] ) ) RETURN IF ( OR ( ISBLANK ( _Msr1 ), ISBLANK ( _Msr2 ) ), 0, 1 )

Measure (Measure 1 / Measure 2) = DIVIDE ( CALCULATE ( SUM ( 'Table'[Measure1] ) ), CALCULATE ( SUM ( 'Table'[Measure2] ) ), BLANK () )

Re: Divide per Category, combining Products

Ankur04 — Mon, 24 Jun 2024 18:41:47 GMT

Hi,

can you explain how come the for category D the measure values is becoming 8?