How to get the store where the customer has bought more?

[GustavO65]

Hi. I have fact table with customers sales.

Our customers buy our products in different stores.

I had no problem showing in a matrix the purchases made by each client in the different stores within a date range.

But as additional information I must show in a column the name of the store where the customer has bought more.

Anyone can help me? Best regards!

[Greg_Deckler]

@GustavO65 This may help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

If that is not specific enough, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[GustavO65]

Hi @Greg_Deckler  tnks for your answer..

I´ll give you more information...

My fact table looks like this...

FACT TABLE

When a user ask for sales between 04-01-2020 and 04-30-2020 I´ll show a matrix like this...

At the moment I didn't find the way to resolve "PRINCIPAL STORE" column...

"PRINCIPAL STORE" is the store where the customer spent more money between selected dates...

[Greg_Deckler]

Right @GustavO65 so that should look something like the following:

PRINCIPAL STORE = 
  VAR __Table =
    SUMMARIZE(
      'Table',
      [STORE],
      "__Amount",SUM('Table'[AMOUNT])
    )
  VAR __MaxAmount = MAXX(__Table,[Amount])
  VAR __Store = MAXX(FILTER(__Table,[__Amount] = __MaxAmount),[STORE])
RETURN
  __Store
  

It's really the pattern I sent before, https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[GustavO65]

Hi @Greg_Deckler  tnks for your help..

Now I´m looking how to display "PRINCIPAL STORE" measure as a Row in my matrix.... because I can only use as a value..

I´ll find information about this...

Regards

[Greg_Deckler]

@GustavO65 - There is a "Display on rows" option for a Matrix visualization in the Formatting pane under I believe Values. Also, there is this article: https://exceleratorbi.com.au/measures-on-rows-here-is-how-i-did-it/

[GustavO65]

@Greg_Deckler  I know "Display on Row" feature (I´m using it)..

But doesnt work for this case.. Please check the matrix I must show as a result.. If I use "Display on Row" the resut is not the expected.

Regards!

[Greg_Deckler]

Right, did you check the article? Because it basically uses something like a disconnected table trick to do it. So does my article here: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/td-p... but a bit differently.