Measure help

[Aysh_2104]

Hello Team,

The Price calculation has to be calculated with the following logic :

1. Check, which price has the highest occurrence in the data set
2. We doesn’t always have a “clear winner” (when only one price has the highest frequency) but there are two or more most frequent price. In this case we calculate the median of these prices with the highest frequency

ID	Product	Price	From	To
1	Apple	10.99	1/1/2021	1/7/2021
2	Apple	10.99	1/20/2021	2/19/2021
3	Apple	11.99	1/12/2021	2/2/2021
4	Apple	10.99	1/13/2021	1/30/2021
5	Apple	12.99	1/29/2021	2/28/2021
6	Apple	10.5	2/10/2021	2/15/2021
7	Apple	12.99	2/22/2021	3/3/2021
8	Apple	11.99	1/25/2021	1/31/2021
9	Apple	11.99	1/30/2021	3/6/2021
10	Apple	10.5	2/9/2021	2/14/2021

Jan Prices	Jan Price Frequencies	Feb Prices	Feb Price Frequencies
10.99	3	10.50	2
11.99	3	10.99	1
12.99	1	11.99	2
		12.99	2

In Jan, 2 price has the same frequency, so the Freq. price is: median(10.99,11.99)= 11.49

In Feb, 3 price has the same frequency, so the Freq. price is: median(10.5,11.99,12.99)=11.99

If there is one clear winner (only one price, with the highest frequency), the median is eg. median(10.99)=10.99

Note :- It should display the output (measure-i.e, Freq price) for the date range of from and to.

Calculation used (to be reworked as it is not giving the right output)

VAR MaxCount =

MAXX ( VALUES ('data'[Price] ), CALCULATE ( COUNT ( 'data'[Price] ) ) )

// RETURN

// CONCATENATEX(

// FILTER (

// VALUES ( 'data'[Price] ),

// CALCULATE ( COUNT ( 'data'[Price] ) ) = MaxCount

// ),

// ('data'[Price]),

// ", "

// )

Return MEDIANX( FILTER (

VALUES ( 'data'[Price] ),

CALCULATE ( COUNT ( 'data'[Price] ) ) = MaxCount), 'data'[Price]). 

Request you to help me with the measure correction to get the output across the date range.

[PaulDBrown]

Interesting challenge, and this is as far as I've got.

(I'm Assuming the data is structured as your first table)

1) Create a Date table.

2) Cretae an active relationship between the date table and the "from" field. Create and inactive relationship between the date table and the "to" field

3) Create a new table (either in Power Query or using DAX) to unpivot the "from" and "to" columns. I've done it using DAX as follows (I'ved named the table "Unpivot": 

Unpivot =
VAR PR =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER ( 'Table', MONTH ( 'Table'[To] ) <> MONTH ( 'Table'[From] ) ),
            'Table'[ID],
            'Date Table'[Date],
            'Table'[ Price]
        ),
        USERELATIONSHIP ( 'Date Table'[Date], 'Table'[To] )
    )
VAR PRT =
    SUMMARIZE ( 'Table', 'Table'[ID], 'Date Table'[Date], 'Table'[ Price] )
VAR UNP =
    DISTINCT ( UNION ( PRT, PR ) )
RETURN
    UNP

To get:

 4) Create a relationship between the Date table and the Date field in 'Unpivot'

The model looks like this:

 5) Create a new column in 'Unpivot' to get the frequecies of prices by Price using:

Price Freq =
VAR UPMonth =
    MONTH ( Unpivot[Date] )
VAR Val = Unpivot[ Price]
RETURN
    COUNTROWS (
        FILTER ( Unpivot, Unpivot[ Price] = Val && MONTH ( Unpivot[Date] ) = UPMonth )
    )

The Unpivot table looks like this

Now the measures:

UP Price = 
MAX(Unpivot[ Price])

Median by Frequency =
VAR MNTH =
    MAX ( 'Date Table'[MonthNum] )
VAR _Frq =
    MAX ( Unpivot[Price Freq] )
VAR _Med =
    MEDIANX (
        ADDCOLUMNS (
            FILTER (
                ALL ( Unpivot ),
                MONTH ( Unpivot[Date] ) = MNTH
                    && Unpivot[Price Freq] = _Frq
            ),
            "PC", [UP Price]
        ),
        [PC]
    )
RETURN
    _Med

Total Median =
MEDIANX (
    ADDCOLUMNS (
        SUMMARIZE ( Unpivot, Unpivot[Price Freq], 'Date Table'[Month] ),
        "Med", [Median by Frequency]
    ),
    [Med]
)

Now you can set up the visuals using the date fields from the Date table and measures:

I've attached the sample PBIX file for you

[Aysh_2104]

@PaulDBrown also what i see here is just for one product, this is just a sample, but I have tons of products in the data. 

[PaulDBrown]

It would help if you provided a sample with the structure of your data. I'm only guessing with this, so:

1) From this table

2) create a calendar table

3) create a new table by merging with the dates field in the calendar table to add the rows needed to cover the dates

 4) Structure the model

5) Create the measures

Price Value = 
AVERAGE(NewTable[ Price])

Frequency =
VAR PRCS =
    AVERAGEX (
        SUMMARIZE ( NewTable, CalendarTable[Month], 'Dim ID'[ID], DProduct[Product] ),
        MAX ( NewTable[ Price] )
    )
VAR MNTH =
    MAX ( CalendarTable[Month] )
VAR Prod =
    MAX ( DProduct[Product] )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( NewTable, CalendarTable[Month], 'Dim ID'[ID] ),
            FILTER (
                ALL ( NewTable ),
                RELATED ( CalendarTable[Month] ) = MNTH
                    && [Price Value] = PRCS
                    && RELATED ( DProduct[Product] ) = Prod
            )
        )
    )

Median =
VAR Frq =
    MAXX (
        ADDCOLUMNS (
            SUMMARIZE ( NewTable, CalendarTable[Month], 'Dim ID'[ID], DProduct[Product] ),
            "Freq", [Frequency]
        ),
        [Freq]
    )
VAR MNTH =
    MAX ( CalendarTable[Month] )
VAR Prod =
    MAX ( DProduct[Product] )
RETURN
    MEDIANX (
        CALCULATETABLE (
            SUMMARIZE ( NewTable, CalendarTable[Month], 'Dim ID'[ID] ),
            FILTER (
                ALL ( NewTable ),
                RELATED ( CalendarTable[Month] ) = MNTH
                    && [Frequency] = Frq
                    && RELATED ( DProduct[Product] ) = Prod
            )
        ),
        [Price Value]
    )

I've attached the sample PBIX file

[Aysh_2104]

@PaulDBrown  But does it covers the inbetween date range, example ID 9 has from Jan to March, so i need the price to be shown in all 3 months as the feb month also falls in the date range

[lbendlin]

Your sample data is inconsistent. You have date ranges overlaps with different prices.

Does "Highest Frequency" look at day level granularity? If so then you can use the built-in DAX functions for Median.

[Aysh_2104]

@lbendlin , Yes, the prices are date ranges are overlaping, the price may be from different vendors. but thats the challenge I have.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. 

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523