New Measure within a visual calculate row value X total

[apermeh]

Hello, new to BI and new to this forum.  Any help will be greatly appreciated.  

I am working on a new project to compare our sales rep progress vs. the total state.  I almost have it complete but I know that I need to do some manual calculations within the visual to get the correct data.  Here is my dilemma:

In the picture below, I have a list of brands that changes automatically when I select the sales rep from the dropdown.  I need to create a "New Measure" that will calculate the total items by the grand total accounts.  I can see that my issue is that the "Total Accounts" column changes to the number of accounts he has sold that item into.  This doesn't give me the actual % that he has achieved in his entire territory.  I have added a second visual above that also shows the total accounts but I don't know if that is something I can use for the calculation either.

I have spent countless hours trying to figure this out and I hope there is a solution.  Thanks in advance for any help!!

[apermeh]

Thanks for the quick reply.  Unfortunately, this didn't work for me.  I am not able to access the specific tables when I use the "SUM" function.  I am not sure why this is, but I was able to complete the following calculation with my target metrics but it doesn't return the expected value. 

SalesRepPercentage =

DIVIDE(

    [Total PODs],

    CALCULATE(

        [Total Accounts]*[Total Items],

        ALLSELECTED()))

Here is the formula that I am trying to achieve:  [Total PODs]/([Total Items]*SUM([Total Accounts].  In the picture below looking at  21 Seeds, it would return the following (37/(3*71)) = (37/213) = 17.3%

I was able to use the following measure and it pulled in the correct count of "71" for every line item but it dropped the supplier filter and was showing every item in our system, not just the items for the specific supplier.

SalesRepPercentage =

    CALCULATE(

        [Total Accounts],

        ALLSELECTED()))

I really appreciate the help with this.

[Anonymous]

Hi, @apermeh 

Thank you very much for your reply. When you use the ALLSELECTED function, contextual filters are removed from the columns and rows in the current query, while all other contextual filters or explicit filters are retained. 

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries. This could be the reason why you are losing your supplier filter. 

Is the supplier filter result missing in the second table visual? Is the supplier filter not missing in the fourth table visual?

Maybe you can try the following DAX:

SalesRepPercentage =
DIVIDE(
    [Total PODs],
    CALCULATE(
        [Total Accounts]*[Total Items],
        ALLEXCEPT('table', 'table'[supplier])
    )
)

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, @apermeh 

You create a Total Account measure, which in the table visual shows 71. You've also created a second visual, and in the second visual, the measure still shows 71, which means that your measure is correct and you can use it for calculations. If you need to get a percentage, you can create a new metric based on this metric with the DIVIDE function to show the percentage for the entire territory. 

SalesRepPercentage =
DIVIDE (
    SUM ( 'Table'[Total Items] ),
    CALCULATE (
        SUM ( 'Table'[Total Accounts] ),
        ALLSELECTED ( 'Table'[Sales Rep] )
    )
)

If this metric doesn't match the actual data, then it may be wrong. If you still have questions, can you describe in detail the results you were expecting?

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.