Only Showing Values in Table That Apply to both Row and Column

[seanpratt]

It's funny - I think I'm okay to Power BI until I have to do something I've never done before... hoping this is pretty easy.

I have a table that's just UserID, DateofVisit, AmountSpent.... that's it. I have a calendar table as well.

All I want to do is create a table in PowerBI that shows the a representation of people that visited between this range of times (column) spent between this amount of money (rows). So if USER ID A1 spent 3,000 and visited 2x and USER ID A2 spent $1500 and visited 1x, the sum under 0-3 and beside 2k to 5k would be $4500

I've attached my excel representation of this. I'm sorry if this is beyond a dumb question!

[edhans · Accepted solution]

See this PBIX for what I did.

I created 2 calculated columns to get the visit and spend ranges:

Spend Range = 
VAR SpendRange =
    CALCULATE(
        SUM(Visits[Amount Spent]),
        ALLEXCEPT(Visits,Visits[User ID])
    )
RETURN
SWITCH(
    TRUE(),
    SpendRange >= 0 && SpendRange <= 1000, "0 - 1K",
    SpendRange >= 1001 && SpendRange <= 2000, ">1K - 2K",
    SpendRange >= 2001 && SpendRange <= 3000, ">2K - 3K",
    SpendRange > 3000, ">3K"
)



Visit Range = 
VAR VisitCount =
    CALCULATE(
        COUNTROWS(Visits),
        ALLEXCEPT(Visits,Visits[User ID])
    )
RETURN
SWITCH(
    TRUE(),
    VisitCount >= 0 && VisitCount <= 3, "0 - 3",
    VisitCount >= 4 && VisitCount <= 6, "4 - 6",
    VisitCount >= 7 && VisitCount <= 0, "7 - 9"
)

Because the Spend Ranges will not sort properly, I created a new table in Power BI to just have the ranges and the sort priority:

There was no need for the Visit ranges since as labeled, alphabetical sort would work. However, if you have visits above 9, you'd need to do the same type of table since 10 would sort before 4 alphabetically.

Then in the model, I related this Range table to my data table.

Added a sum measure:

Total Spent = SUM(Visits[Amount Spent])

Then dropped the fields in this matrix and told it to sort the Range by the Range Sort field in the Modeling tab.

See Sort By Columns article for specifics on what I did there if you aren't aware of that feature.

If I've not met your goal or you have questions, let me know! 

[seanpratt]

Wow ! That worked perfectly. 

Of course, my boss says "What if we wanted to change or compare the ranges?" - So, would it be possible to have another column in the Spend Range (sort) table called "Preset" and then have a number for each range belonging to one set?

And then in your Spend Range calculated column, could you somehow specify IF(Preset = 1, SWITCH(OptionsforSpendRangeOption1), IF(Preset = 2, SWITCH() Range options for preset 2)

Then just add a slicer and have the end user select what Range Option to apply? Does that make sense?

[edhans]

Yes. In fact, you can just ignore the ">$1K and <$2K" columns in the main table and have them just return 1, 2, 3, 4, 5, etc. You could then actually use that column as a relation to the Range Sort column in the other table. Then you just need to maintain that table.

You can go further and put in an upper and lower limit to the ranges in the range table and get rid of the SWITCH() function. For example

Spend Range 2 = 
VAR SpendRange =
    CALCULATE (
        SUM ( Visits[Amount Spent] ),
        ALLEXCEPT ( Visits, Visits[User ID] )
    )
RETURN
    CALCULATE (
        MAX ( 'Spend Sort'[Range Sort] ),
        FILTER (
            'Spend Sort',
            SpendRange >= 'Spend Sort'[Lower Range]
                && SpendRange <= 'Spend Sort'[Upper Range]
        )
    )

That will return 1, 2, 3, or 4 based on the data in my Range Sort table. Go get my PBIX file again. I saved this info in there.

Your Spend Sort table should be something your boss can easily edit. A SharePoint list, for example where the ranges can be tweaked and then the report updates as applicable. You will have to be careful though that no values are missed. The upper limit of row 1 must be $1 less than the lower limit of row 2, etc. any missed amounts will cause the row to return a blank and will vanish from your report until the table is fixed.

Please be sure to mark it as the solution to  your answer.

[seanpratt]

Thanks for that. A couple questions though. We have a few "defined" ranges - this was what I had in mind. A slicer to determine whether to pick range 1, 2, 3 - it automatically changes those row ranges but how do I assign the calculation to "slot in" with each range? I'm trying to work it in to your Calculated Column to say "IF(Preset=#, SWITCH(TRUE(), etcetc)...

Additionally, is there a way to make this a time-based calculation? I've attached my own worksheet and it looks as though User BBB shouldn't be in range >10K in February 2020, but because their grand total is greater than 10k, it's already showing in the wrong column. They should be in 5k-8k. See below. The original solution didn't factor in the time dimension

[seanpratt]

Any luck on solving this? The time-based function of the calculation is most important at the moment. The preset filtering is secondary to the main issue

[edhans]

Hi Sean, this seems to be turning into more of a full project than help with Power BI. The initial request I responded to didn’t mention time at all and the fake data I created doesn’t have anything like that. So it seems I kinda spent an hour or so going down the wrong path.

If someone wants to jump in they can. I a may be able to find time to work on it more, but I’m really here to help users with specifics questions. Full projects obviously involves more time and knowledge of the requirements. I hope you can understand that.

[seanpratt]

Hi @edhans ,

I completely get it. My original ask was one of those "this can't be this complicated, can it?" type questions and I even said I thought your solution had it. I'm always so pleased with the time calcs within Power BI so I didn't even assume that it wouldn't be updating the table. 

I really appreciate all of your help with this one. I think we might as well put it to bed. I've chatted with a few other data experts within my company today and all of them gave me kind of a blank stare. 

Sorry to be unclear initially but the ask did change and I was hoping the solution was just a plug in. Guess not!

Thanks again!