Median of ratio

[ddanci]

Hi,

I have a table with 3 columns (Item, Price, Quantity) and I need to create a measure that will calculate the median of the ratio between Quantity and Price. Some of the rows do not have Price values.

In the table below I added the Ratio column that is the ratio between Quantity and Price for testing the median by calculating the median of the column Ratio.

But, I do not want to create the Ratio calculated column.

Instead, I want to use a measure that will calculate the ratio and then determine the Median of the ratio and display it at the bottom of the table (where ussually you see the Total for each column).

I created two measures m_Ratio1 and m_Ratio2:

m_Ratio1 = DIVIDE(MAX('Median Issues'[Quantity]),MAX('Median Issues'[Price]))

m_Ratio2 =
VAR Ratio_Table =
SUMMARIZE ( 'Median Issues', 'Median Issues'[Item], "Ratio1", [m_Ratio1] )
RETURN
IF (
HASONEVALUE ( 'Median Issues'[Item] ),
[m_Ratio1],
MEDIANX ( Ratio_Table, [Ratio1] )
)

Below is the Median results of the two measures and the Median calculated for the Ratio column:

The expected result should be 2.26% as determined by calculating the Median of the Ratio column.

But, if I do not use the Ratio calculated column, the measures are producing different Medians from what it should be:

m_Ratio1 = 2.51% and m_Ratio2 = 1.48%.

Please let me now if you can create a measure that will produce the correct Median of a ratio between two columns (without creating a calculated Ratio column).

Thank you.

Note: this is the table data. You can copy it and paste it in Excel.

Item Quantity Price Ratio
A1 136,013 4,500,000 3.02 %
A2 56,493 2,500,000 2.26 %
A3 37,800 4,445,000 0.85 %
A4 10,823 3,000,000 0.36 %
A5 142,795 5,700,000 2.51 %
A6 17,897 850,000 2.11 %
A7 103,728 2,750,000 3.77 %
A8 99,174
A9 8,743
A10 110,836

[Jos_Woolley · Accepted solution]

Hi,

MEDIANX is treating the blanks as zero and including those zeroes in the calculation, which you can easily verify. Simply exclude them, e.g.:

m_Ratio2 =
VAR Ratio_Table =
    SUMMARIZE(
        'Median Issues',
        'Median Issues'[Item],
        "Ratio1", [m_Ratio1]
    )
RETURN
    IF(
        HASONEVALUE( 'Median Issues'[Item] ),
        [m_Ratio1],
        MEDIANX(
            FILTER(
                Ratio_Table,
                [Ratio1] > 0
            ),
            [Ratio1]
        )
    )

Regards

[Jos_Woolley · Accepted solution]

Hi,

MEDIANX is treating the blanks as zero and including those zeroes in the calculation, which you can easily verify. Simply exclude them, e.g.:

m_Ratio2 =
VAR Ratio_Table =
    SUMMARIZE(
        'Median Issues',
        'Median Issues'[Item],
        "Ratio1", [m_Ratio1]
    )
RETURN
    IF(
        HASONEVALUE( 'Median Issues'[Item] ),
        [m_Ratio1],
        MEDIANX(
            FILTER(
                Ratio_Table,
                [Ratio1] > 0
            ),
            [Ratio1]
        )
    )

Regards

[ddanci]

Hi Jos,

The solution you have proposed works perfectly for ratio between columns in the same table.

I would like to ask you for help with a similar situation, but involving 2 related tables, where the relationship is one to many.

You can copy and paste the following tables in Excel.

MedTable1:

Item Adjusted_Rate
A1 31
A4 20
A5 25
A6 28
A7 32

MedTable2:

Item SubItem Actual_Rate
A1 A1a 30
A1 A1b 31
A1 A1c 32
A4 33
A5 34
A6
A7

I created the following table visualization:

The measures are:

m__Ratio1 = DIVIDE(MAX(MedTable1[Adjusted_Rate]),MAX(MedTable2[Actual_Rate]))

m__Ratio2 =
VAR Ratio_Table =SUMMARIZE('MedTable1','MedTable1'[Item],"Ratio1", [m__Ratio1])
RETURN
IF(HASONEVALUE( MedTable1[Item] ),[m__Ratio1],
MEDIANX(FILTER(Ratio_Table,[Ratio1] > 0),[Ratio1]))

These two measures in combination will produce correct results if the related tables are one to one.

The correct Median should be 96.88%, but m__Ratio1 = 94.12%, and m__Ratio2 = 73.53%.

The Median produced by m__Ratio2 of 73.53%, I think is using the Items A5, A4 and one of the A1 triplicates:

As a resulth the Median is calculated as follows:

A1 96.88% or 100.00% or 103.33%

A4 60.61%

A5 73.53%

Median 73.53%

I need help to figure out the correct Median when the related tables are one to many.

Thank you.

[Jos_Woolley]

Not sure I understand. Your m__Ratio1 measure does not involve taking the median anywhere - it's simply dividing the Adjusted_Rate by the Actual_Rate.

Also, not sure how you get a median of 28 for Adjusted_Rate in your table. The median of those 7 values is 31, not 28. 

Regards

[ddanci]

Hi Jos,

The Adjusted_Rate median was calculated by PBI built-in Median:

I know it is wrong, but it may have to do with one to many relationship between the tables?

Regarding the m__Ratio1 measure, I am using it to crete a ratio between Adjusted_Rate and Actual_Rate, for which I need to determine the Median (by using m__Ratio2 measure).

At the end of the day, I need to determine the Median of a ratio between two columns located in two related tables having one to many relationship.

Any way of determining this Median will do; does not have to be the way I envisioned it so far. 

Please let me know if this clarifies my original post.

Thank you,

[Jos_Woolley · Accepted solution]

So you should be using SUMMARIZE on MedTable2, then, down to the SubItem level, I believe:

m__Ratio2 =
VAR Ratio_Table =
    SUMMARIZE(
        'MedTable2',
        'MedTable2'[Item],
        MedTable2[SubItem],
        "Ratio1", [m__Ratio1]
    )
RETURN
    IF(
        HASONEVALUE( MedTable1[Item] ),
        [m__Ratio1],
        MEDIANX( FILTER( Ratio_Table, [Ratio1] > 0 ), [Ratio1] )
    )

Regards

[ddanci]

Hi Jos,

All good.

Thanks a lot.