Ratio % of Particular Sales v All Sales

[Anonymous]

Hello,

I'm trying to build a table to show  sales ratio % where we've used particular suppliers against all sales with any suppliers.  This is year to date. 

KeySuppRatio% = DIVIDE(

[KeySuppYTD],[TotalSalesYTDAllExcept],0)

I've tried using ALLEXCEPT in a measure for all sales with any suppliers:

TotalSalesYTDAllExcept =

CALCULATE(

[TotalSalesYTD],

ALLEXCEPT(DimSalesDetails,DimSalesDetails[SupplierNo]),

ALLEXCEPT(Users,Users[FullName]))

This works okay if you are looking at totals, but I also want to have the values change dynamically when you select a particular user  or team from a slicer.  In this example "Robin Hood" has actually sold 14.29% of key supplier products this year when compared to all his sales (£30006/£210042) - not 3.43% but the calculation is still taking the full sales this year for all users into the TotalSalesYTDAllExcept measure even though I'm asking it to take that users into consideration.  

So, I think I'm using the wrong DAX formula to achieve this if users want to slice the data and change the table.  Would someone be kind enough to point me in the right direction please?  It seems a simple request from my users but I'm struggling to get this right.  I've uploaded some sample data based on the tables and relationships I'm using.

https://drive.google.com/open?id=1_PdEQF5Fd_8oJP3aGyweuWCzVmJUzmDI

Many thanks

[v-lid-msft]

Hi @Anonymous ,

We can use the following measure to meet your requirement:

KeySuppRatio% =
DIVIDE (
    [KeySuppYTD],
    IF (
        COUNTROWS ( 'Users' ) <> COUNTROWS ( ALL ( 'Users'[FullName] ) ),
        [TotalSalesYTD],
        [TotalSalesYTDAllExcept]
    ),
    0
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks.  You can get the same result using 

 DIVIDE( [KeySuppYTD],[TotalSalesYTD],0)

I'd like the end result for 'Robin Hood' to be:

SN001 4.76%

SN002 9.52%

Total 14.29%

This is SN001 (£10002/£210042)

and SN002 (£20004/£210042)

Then the total (30006/210042)

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure to meet your requirement:

KeySuppRatio% = 
DIVIDE (
    [KeySuppYTD],
    IF (
        COUNTROWS ( 'Users' ) <> COUNTROWS ( ALL ( 'Users'[FullName] ) ),
        CALCULATE([TotalSalesYTD],ALLSELECTED('KeySuppliers')),
        [TotalSalesYTDAllExcept]
    ),
    0
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you. That's just what I was looking for  

That's going to really help my users.  Brilliant!

---

@v-lid-msft wrote:

Hi @Anonymous ,

We can use the following measure to meet your requirement:

KeySuppRatio% = 
DIVIDE (
    [KeySuppYTD],
    IF (
        COUNTROWS ( 'Users' ) <> COUNTROWS ( ALL ( 'Users'[FullName] ) ),
        CALCULATE([TotalSalesYTD],ALLSELECTED('KeySuppliers')),
        [TotalSalesYTDAllExcept]
    ),
    0
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---