Average Sales At Price Point - Dynamic X Axis Using Dax

[jaqcues]

I am trying to create a chart which displays average dollar sales and # of weeks at certain price levels (increments of 10c) over the last 52 weeks.

Below is an example of how I would like it to look, however this chart is currently incorrect for reasons I will go into below.

The above was made by adding to the fact table (nz_fact_iri) a calculated column for price (dollars/units) rounded up to nearest 1 digit in text format, then using a dimension table with the same values on the x axis, and a basic countrows formula to get the count of weeks at this price. The problem with this method is the chart is only correct at the lowest level, product_description and market_description. If I use any aggregated products (dimension product_fpg) or markets (dimensions banner, account etc) in my slicers and the product_description or market_descriptions from the same group have a different average sale price from each another, the number of weeks per year goes up since the price is static per product - whereas I want the average price to be dynamic depending on whatever products or markets are selected.

As an example if product A's average price/unit was $1.13 and product B's was $1.21 in the same week, selecting A and B in my product filter would result in 2 different weeks on my chart (at <=$1.20 and <=$1.30). I want the price to be averaged within a measure at $1.17, for one week on the chart at <=$1.20).

Below I have marked the relational columns - date, market_description and product_aztec_code. Also below are shortened versions of relevant Dim tables and the fact table.

 

Dim_Account

market_description	Banner	Region
StoreA UNI	StoreA	UNI
StoreA SI	StoreA	SI
StoreA LNI	StoreA	LNI
StoreB UNI	StoreB	UNI
StoreB SI	StoreB	SI
StoreB LNI	StoreB	LNI

Dim IRI_Prod_Attr

product_aztec_code	product_description	product_fpg	product_category	product_manufacturer	product_brand
1897767	Abcd Vitamin Water Vitamin B Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	Water	Other Mfrs	Abcd Vitamin Water Brand
1897769	Abcd Vitamin Water Vitamin D Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	Water	Other Mfrs	Abcd Vitamin Water Brand
1897768	Abcd Vitamin Water Vitamin C Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	Water	Other Mfrs	Abcd Vitamin Water Brand
1897766	Abcd Vitamin Water Vitamin A Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	Water	Other Mfrs	Abcd Vitamin Water Brand

nz_fact_iri - contains weekly data

dollars	units	product_aztec_code	product_description	product_frucor_promo_group	market_description	iri_calendar_date
1469.7	355	1897767	Abcd Vitamin Water Vitamin B Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreA UNI	6/03/2022
769.5	190	1897769	Abcd Vitamin Water Vitamin D Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreA SI	14/03/2021
2554.7	590	1897768	Abcd Vitamin Water Vitamin C Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreA LNI	5/12/2021
106.9	21	1897766	Abcd Vitamin Water Vitamin A Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreB UNI	9/05/2021
710.7	149	1897767	Abcd Vitamin Water Vitamin B Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreB SI	16/05/2021
1462.5	404	1897769	Abcd Vitamin Water Vitamin D Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreB LNI	11/07/2021
866.4	190	1897768	Abcd Vitamin Water Vitamin C Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreA UNI	27/06/2021
1221.5	348	1897766	Abcd Vitamin Water Vitamin A Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreA SI	28/11/2021
1128.1	238	1897767	Abcd Vitamin Water Vitamin B Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreA LNI	23/01/2022
4237.1	1021	1897769	Abcd Vitamin Water Vitamin D Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreB UNI	27/02/2022
1100.4	244	1897768	Abcd Vitamin Water Vitamin C Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreB SI	25/07/2021
1254	293	1897766	Abcd Vitamin Water Vitamin A Sipper Top 500ml	Abcd Vitamin Water 500ml Fpg	StoreB LNI	27/02/2022

I have tried another method which is was sure was a winner - but it is just showing blank values.

I created a calculated table called Histogram Axis using Generateseries with .1 increments, then used this measure:

Price Count = 
VAR HistogramColumn =
    SELECTEDVALUE ( 'Histogram Axis'[Value] ) 
VAR AvgPriceTable =
    SUMMARIZE (
        nz_fact_iri,
        nz_fact_iri[iri_calendar_date],
        "Price",
       ROUNDUP( [Dollars] / [Units],1)
    )
RETURN
    COUNTROWS (
        FILTER (
            AvgPriceTable,
            [Price] = HistogramColumn  
        )
    ) 

Any guidance would be much appreciated! This is my first post so please let me know if I have left out any details.

[jaqcues]

Thank you so much! This method worked perfectly - exactly what I was looking for.

[v-shex-msft · Accepted solution]

HI @jaqcues,

According to your description, it seems related to segment requirements. If that is the case, you can take a look at the following link about the dynamic segment if helps:

Dynamic segmentation – DAX Patterns

Regards,

Xiaoxin Sheng