Perform a rank based on a measure combining multiple tables

[RogerSteinberg]

Hi,

I would like to products based on measure that computes over two columns from two different tables.

I have 4 tables:

Table 1: Product Names

Table 2: Dates

Table 3: Cost 

Table 4: Revenue 

I have one measure:

Profit to Revenue: 
DIVIDE(
SUMX(Revenue,Revenue)-SUMX(cost,Cost),

SUMX(Revenue,Revenue)

)

I have two fact tables (cost, revenue) both related to the product name and date tables as a many:1 relationships

I'd like to rank my products in a descending order by total cost after having filtered that table by profit margin > 0.50.

Current Table looks like this : 

Product (Product Name Table)	Cost (Cost Table)	Revenue (Revenue Table)	Profit To Revenue (Measure)
A	$                 10.00	$                                 20.00	$                                          0.50
B	$                   5.00	$                                 15.00	$                                          0.67
C	$                 10.00	$                                 50.00	$                                          0.80

Desired Output: As you can see the rank is based on the cost column after having filtered out products that had 0.50 and lower Profit to revenue value.

Product (Product Name Table)	Cost (Cost Table)	Revenue (Revenue Table)	Profit To Revenue (Measure)	Rank
B	$                   5.00	$                                 15.00	$                                          0.67	2
C	$                 10.00	$                                 50.00	$                                          0.80	1

One import thing to consider is that I have a date slicer that always selects a date range. So that rank needs to be applied on the aggregate values for that period . Same goes to the filter of 0.50 which should be applied on the date range as well.

[amitchandak]

@RogerSteinberg , refer if these can help

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[RogerSteinberg]

Hi, thanks for the documentation but the issue i'm having doesn't lie in making a simple rank function, it is when trying to filter a table prior to ranking when my main measure involves two tables that are indirectly connected.

[v-deddai1-msft]

Hi @RogerSteinberg ,

You may need a measure like:

RANK =
IF (
    ISFILTERED ( [Profit To Revenue] ),
    RANKX (
        FILTER ( ALL ( 'Product Name'[Product] ), [Profit To Revenue] > 0.5 ),
        CALCULATE ( SUM ( 'Cost'[Cost] ) )
    ),
    RANKX ( ALL ( 'Product Name'[Product] ), CALCULATE ( SUM ( 'Cost'[Cost] ) ) )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[RogerSteinberg]

You cant pass the measure in the first expression of ISFILTERED.
Plus having the ALL would rank my entire dataset even if I have a date slicer of my chosen period which defeats the purpose.

[v-deddai1-msft]

Hi @RogerSteinberg ,

The reason why I added measure to the isfiltered function is because I think you added [Profit To Revenue] to the visual level filter for filtering. This works very well in the sample I created myself. In addition, I use the all (column) just cleared the filter on this column, if you have other slicers you can use allselected (column) instead.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[RogerSteinberg]

No what I mean is that the measure that I created Profit to Revenue can't be passed in the ISFILTERED function.

I believe it can only accept a Column