Exclusive Data

[dmb33875Participant Started: 3Replies: 5Total: 8]

I have a list of customers for whom sales could be from any one of three profit centers or a combination therein.  It’s not proprietary so I’ll share that those profit centers (PC) are 210, 220 and 230.  The of my report is to show customers from whom sales come from a single PC, a combination of PCs or all PCs with the idea that we want to show either our ability to or opportunities to leverage sales.

I’m able to isolate and effectively demonstrate these sales using measures that I’ll share below as visual level filters.  However I want to take it to the next level to show those sales as a percentage of total sales.  My difficulty is that my visual level filters are hanging me up from seeing all the data so I need smarter measures to achieve my goal.

As a final preface for this report I’ll tell you that I am filtering on customer names with sales amounts.  It’s fairly straight forward, but note that these are sales by order and not sales by customer so a single customer could have dozens, hundreds or thousands of lines of data across one, some or all PCs.

First I’m using one of three measures to give a yes/no answer to determine if the customer has sales in a specific profit center:

• is210Amt = if(calculate(sum(Query1[USD Amount]), Query1[PC]=”210″) >0, 1,0)

Then I use a second measure that I’ll use as a visual level filter to carve out those customers with sales exclusive to a single PC:

• is210_Only = if(AND(AND([is220Amt] = 0, [is210Amt]=1), [is230Amt] = 0), 1, 0)

Finally I use some secondary measures also used as filters to find customers using at least two PCs or all PCs:

• is210_220 = if(AND(AND([is220Amt] = 1, [is210Amt]=1), [is230Amt] = 0), 1, 0)

I have tried a number of measures using CALCULATE to try and isolate these sales.  However the issue that I am running into with these measures is that they can recognize sales by PC, but not when sales are exclusive by PC.  If anyone has a smarter method of doing this then I’m open to suggestions.

[dircurParticipant Started: 0Replies: 1Total: 1]

Hi i would suggest watching the Calculate Sample video that is available on the course registration page.

https://powerpivotpro.com/self-paced-online-video-training/

I am totally not affiliated but I would bet you will be able to build the measure. I’m newb but you haven’t gotten a reply so i will give it a go. I am making some assumptions here. I am assuming your your conditionals are working and you want a result similar to:

Client        210_%Sales       220_%Sales     230_%Sales    Total
ABC                 15%                      85%                    0                 $20,000
DEF                 0                           100%                  0                 $45,000

Here are your measures and you would only pick the Client, % columns, and the Total_Sales column for your visual.

Total_Sales = SUM(Query1[USD Amount])
210_Sales = CALCULATE(Total_Sales,Query1[PC]=”210″)
220_Sales = CALCULATE(Total_Sales,Query1[PC]=”220″)
230_Sales = CALCULATE(Total_Sales,Query1[PC]=”230″)

210_%Sales = 210_Sales/Total_Sales
220_%Sales = 220_Sales/Total_Sales
230_%Sales = 230_Sales/Total_Sales

If these work  WATCH THAT VIDEO!!

[Author]

Posts