DAX: How can I calculate MODE for a Reference Table?

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?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top