Forum Discussion

LorneS's avatar
LorneS
Copper Contributor
Oct 16, 2021

Complex calculations on a pivot table

I have a large database and pivot table, with monthly data. I created a sample database to show you the problem I am having.

The underlying data looks something like this (just clipped a few lines)

 

accountnamemonthsales
1090HertzJan-2113
1090HertzFeb-217
1090HertzMar-218
1090HertzApr-212
1121AvisJan-214
1121AvisFeb-217
1121AvisMar-219

 

The resulting pivot returns this

 

What I want to do is display only the accounts where sales in 2 of the last 3 months are 5 or less.

I tried calculated items - but on my original pivot table this results in an error " too many records" 

I've tried other iterations, none work without a ton of manual intervention.

Can someone point me in the direction of a solution or a hack?

 

1 Reply

  • Yea_So's avatar
    Yea_So
    Bronze Contributor

    LorneS SergeiBaklan 

     

    Load your data set into the data model, and create a DAX measure.  See tutorial below

     

    https://youtu.be/e-CFYi52gpc

     

    If you have a DAX measure question, ask it here Mr. Sergei Baklan is the DAX expert in the community

Resources