Showing weekly most frequent price in table/matrix

[YPr]

Hi all, 

I am adding excel files to a folder on a weekly basis, which includes a long list of products with prices per each product in different regions ('North', 'Southwest', Southeast') etc. 

The PowerBI file is set up to include the files uploaded, has column for week number, product ID, product name, dsitributor/supplier, price North, price Southeast, price Southwest, etc. 

I want to show this information in a table/matrix and add a mode column to show the most frequent price OR (if possible) the minimum price (if no mode or multiple). The prices could show as an average or per product (via slicers and filters). 

I tried multiple methods, both with the regional price column unpivoted and pivoted, but having a difficult time finding a working solution for the mode on PBI. Any help is appreciated!

[JoaoMarcelino]

Hi @YPr 🙂

I'm happy to have helped!

Regarding your other question.. Is this what something like what you are looking for?

If so, you could performe the following changes:

1- New measure Mode Price 2:

ModePrice 2 = 
VAR ProductPrices = 
ADDCOLUMNS(
    SUMMARIZE(Sales, Sales[Price]),
    "PriceCount", CALCULATE(COUNTROWS(Sales))
)
VAR MaxCount = MAXX(ProductPrices, [PriceCount])
VAR ModePrices = FILTER(ProductPrices, [PriceCount] = MaxCount)
VAR ModePriceValue = IF(COUNTROWS(ModePrices) = 1, MAXX(ModePrices, Sales[Price]), MIN(Sales[Price]))
RETURN
ModePriceValue

2- New measure, Final Model price:

FinalModePrice = 
IF(
    HASONEVALUE(Sales[Product id]),
    [ModePrice 2],
    MINX(VALUES(Sales[Product id]), [ModePrice 2])
)

3- Drag and drop just this measure:

Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂
Find me on linkedin

[YPr]

Hi Joao,

Thanks again, that is really super helpful:) 

The only case where I see it not showing rhe correct number in the total is in the cases where there are multiple equal modes and the one lower frequency number, it then chooses the lowest number even though it is not more frequent (so ideally it would return the lower mode number). See the highlight below.

And one last question - how would you recommend showing this Total in a Line chart? It would be great to show the different regions as lines and then a line for the mode. If you have any idea that would really be great, otherwise I am already grateful for the super helpful support 🙂

[JoaoMarcelino]

Hi @YPr ! 🙂

I'm happy you found it helpful 🙂

So is this what you are looking for?

When two modes with the same frequency happen ( 9 x 2 and 13 x 2), the lowes mode value is selected (9).

If so, you could try the following measure:

ModePrice 3 = 
VAR ProductPrices = 
    ADDCOLUMNS(
        SUMMARIZE(Sales, Sales[Price]),
        "PriceCount", CALCULATE(COUNTROWS(Sales))
    )
VAR MaxCount = MAXX(ProductPrices, [PriceCount])
VAR ModePrices = 
    FILTER(
        ProductPrices, 
        [PriceCount] = MaxCount
    )
VAR ModePriceValue = 
    MINX(ModePrices, Sales[Price])
RETURN
ModePriceValue

Its not that beautiful but in my tests it did the trick 🙂

Regarding you second question...
Thats a bit trickier, but I see two options:
- Field Parameter;
- Bookmarks

In either, the trick is to switch between two measures, Mode Pirce 3 and the new measure Total Mode.

Total mode = 
CALCULATE(
    [ModePrice 3],
    ALL(Sales[Zone])
)

This will ignore the Zone "effect" and will give you something like:

or this:

Now you need to choose one of several options. Two options cross my mind atm: field parameter ou bookmark.
If you choose bookmarks, just create two equal charts, one with the measure Model Pirce 3 and other with me measure Total Model, create two bookmarks (one that hides the 1st visual and show the second and other that does the opposite) and then add, for example, two buttons and give to each button the corresponding bookmark action. If creates a cool effect if both line charts are overlaping and seem to be the "same".

The other option crossing my mind is to create a field parameter to switch between the two measures:

Call it, for example, Mode Switch and drag and drop it to where the measure used to be

The, select the slicer and make it more appealing, by choosing Tile instead of list or dropdrown:

Now you can switch between both measures:

The is a limitation though in this option: there isn´t an option to change colors in a line chart while using a field parameters (well, there might be but I am not aware of it) so to make it have different colors while switching (at least kind of) you can try to with the transparency so that it "changes" colors a bit:

In terms of doing the adjustments and maintance I would probably go with the bookmarks option....

Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂
Find me on linkedin

[JoaoMarcelino · Accepted solution]

Hi @YPr 🙂

I didn't understand if you have a column with "zone" (North, price Southeast, price Southwest) and a column with the respective price or if you have 3 columns "Price North, price Southeast, price Southwest" with the respective price.

If the 2nd option is the case, I would first select those 3 columns, unpivot them and get 2 columns "Zone" and "price". That would make everything much easier to calculate.

I don't have your table or model, but maybe you could try something like this:

1- Before unpivoting:

2- After unpivoting:

We can visually see tha for Ball, the mode price is 12 and for small ball the mode price is 9

3- Load data and create a new measure, for example, something like:

ModePrice = 
VAR ProductPrices = 
ADDCOLUMNS(
    SUMMARIZE(Sales, Sales[Price]),
    "PriceCount", CALCULATE(COUNTROWS(Sales))
)
VAR MaxCount = MAXX(ProductPrices, [PriceCount])
VAR ModePriceValue = MAXX(FILTER(ProductPrices, [PriceCount] = MaxCount), Sales[Price])
RETURN
IF(ISBLANK(ModePriceValue), MIN(Sales[Price]), ModePriceValue)

4- Play with filters, fields and context and test results 🙂

You can also drag an drop the field "Week number" to your matrix:

 
Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂
Find me on linkedin

[YPr]

Hi Joao, 

Thank you very much for the reply, and that seems to have worked pretty well!

The only thing, to make it really perfect, is if there is no clear mode (no numbers appear more than once, or there are multiple with the same frequency) it would be ideal to show the lower price in these cases. Is there a way to do this? 

And then, as far as integrating this into a visual. I would love to show this in a matrix and line chart, in front of the other regions. But when adding to the matrix, it gives the ModePrice next to each column, instead of only once at the end.. In the meantime I have created a seperate matrix just for this, but would be amazing to have it in one item.. 

That is just extras - if they can be added it would be great. Thank you very much for the help already given!