CUBEMEMBER for pricing dashboard

Question

Hello,

I'm trying to create a dashboard of competitor pricing that we have on record.

Please see link below for an example of the format:

(The data is under the "Data" sheet)

CUBE Value example for sharing.xlsx

I'm open to other ways this could be done as well (e.g power BI or pivot tables or different formulas etc).

1. For competitor 1, we want to display their most recent pricing, so want to return the max date for competitor 1, and the corresponding price.

To return the max date, I tried to use this formula =CUBEMEMBER("ThisWorkbookDataModel",{"[Table15].[Competitor].&[Competitor 2]","[Measures].[Max of Price]"}), and change [Max of Price] to [Max of Date], however this returned a #N/A error. Please can someone give advice on how CUBEMEMBER can be used to solve this?

(I have previously tried using a INDEX(MATCH(MAXIFS for the max date and a IF(ISNUMBER(MATCH (INDEX(MATCH for the corresponding price to achieve this, however it is too slow, even with only 100 lines of data. The data was in a different format with each competitor price as a separate column, and the products and dates as the rows)

2. For competitor 2, we want to display their highest and lowest prices, so we want to return the max price for competitor 2 and the corresponding date, and the min price for competitor 2 and the corresponding date. Please can someone give advice on how I could incorporate a CUBE formula for this as well?

The yellow headings is the suggested headings, the white headings are the existing CUBEMEMBER headings.

Hopefully someone can help, and let me know if you need more information.

Thanks in advance.

Answer 1

"The data must be prepared, which is not that easy. "

Also, your comment above, could you explain this for me?

As I don't have experience with power pivot, so not sure how to handle this data from the start.

Sample file updated, Recent date bug fixed.

I prepared the data using Power Query, you can not just load the data into Power Pivot / Data Model and get the result. (To be honest, this may be possible, I am not an expert on DAX. But I have enough experience to be sure that this is very complicated.)

In my file are 4 queries to make it simple. The Data query simply load the data as connection only. The Recent query refers to the Data query, group the data by the max. Date and get the related Price.

The MinMax query is nearly the same, but does 2 aggregations to find the min./max. Price and get the related Date.

The final query is the Model query, in this query I combined the Recent and MinMax query using a left outer join, same way as Herbert does in his file. This query is loaded into the Data Model, all others are a connection only.

With my preparation I'm done and I can create a Pivot table and drag the fields wherever I want.

Herbert's solution is similar, he has created some measures to find the results.

If this is all new to you, then the easiest way is to copy your data into the table in my example file, click Data \ Refresh All and you're done.

Do not change the headers of the table, that breaks the queries and so the model!

Andreas.

Answer 2

"The data must be prepared, which is not that easy. "

Also, your comment above, could you explain this for me?

As I don't have experience with power pivot, so not sure how to handle this data from the start.

Answer 3

Hello Andreas,

Thank you very much for your help with that, that is very useful!

Regarding the multiple units for one product - sorry my data wasn't entered correctly, there will only be one unit for each product (e.g product 4 will always be Each, product 6 will always be /m2).

Please see below updated file.

From microsoft community.xlsx

Just a question below:

It appears that the Recent date is the lowest date?
For example product 6 for competitor 2, the 2 entries are 5/01/2020, and 11/01/2020, but the Recent column is returning 5/01/2020 and the price of 5/01/2020.

Maybe you know how to fix this?

Answer 4

Processing the data in this way will be either very difficult or impossible.

You forgot to take the unit into account in your comparison, this is where your data differs. For example, for Product1 there are 3 different units, depending on the competitor.

The data must be prepared, which is not that easy. I have chosen a slightly more extensive method to make the analysis of the final price easier. I have only checked the results on a sample basis, please take a closer look.

Sample file:
https://www.dropbox.com/scl/fi/valxdsokcr1ged2irbiy3/f743dbd6-ade4-4b0b-86d1-e8c935966811.xlsx?rlkey=fha2nwikr8d132mf3ebiocy49&dl=1

There is a slicer in column A where you can select the competitors you want to compare. The pivot table shows the results for each competitor individually, so you can compare as many competitors as you want at the same time.

Andreas.

Answer 5

Excel 365 Pro Plus with Power Pivot and Power Query.

Min/Max Price/Date

No formulas, no cubes, no VBA macro.

With PivotTable, PivotChart and Slicers.

https://www.mediafire.com/file_premium/6zbze4n7mhi75xu/03_05_25a.xlsx/file

https://www.mediafire.com/file_premium/0jn1rqbwh7byxef/03_05_25a.pdf/file