moosetracks
New Member
- Joined
- Dec 20, 2016
- Messages
- 4
Hi everyone,
Thanks to anyone who can help. I hope this makes sense since I'm not very skilled at technical communication.
SCENARIO
I'm looking to get the MODE of each item on a reference table in Power Pivot. The scenario is that I have a list of vendors on a reference table. Then I have a data table with bills for services from these vendors. What I want to do is get the MODE of discounts (%) on past bills for each vendor so I can see any bills missing the usual discount from that vendor.
FIRST ATTEMPT
I already tried putting it into the reference table before linking it to the data model with an array formula that sort of worked. But it took way to long and I had to refresh it manually. I would rather have it calculated inside my data model.
SECOND ATTEMPT
Then I tried using a measure. The measure did what it's supposed to, but it doesn't do what I want it to. I want the mode for each vendor to be the same no matter its coordinates in a pivot table.
POSSIBLE SOLUTION
I have a feeling that I will need to make a calculated column for this. I was hoping to avoid that because my data model is big and slow enough already. Still, I cannot find any suggestions online on how to achieve the MODE on each row of a reference table and my skills with statistical formulas is nil. Could someone point me in the right direction?
Thanks to anyone who can help. I hope this makes sense since I'm not very skilled at technical communication.
SCENARIO
I'm looking to get the MODE of each item on a reference table in Power Pivot. The scenario is that I have a list of vendors on a reference table. Then I have a data table with bills for services from these vendors. What I want to do is get the MODE of discounts (%) on past bills for each vendor so I can see any bills missing the usual discount from that vendor.
FIRST ATTEMPT
I already tried putting it into the reference table before linking it to the data model with an array formula that sort of worked. But it took way to long and I had to refresh it manually. I would rather have it calculated inside my data model.
SECOND ATTEMPT
Then I tried using a measure. The measure did what it's supposed to, but it doesn't do what I want it to. I want the mode for each vendor to be the same no matter its coordinates in a pivot table.
POSSIBLE SOLUTION
I have a feeling that I will need to make a calculated column for this. I was hoping to avoid that because my data model is big and slow enough already. Still, I cannot find any suggestions online on how to achieve the MODE on each row of a reference table and my skills with statistical formulas is nil. Could someone point me in the right direction?
