Percentage of total using lookup table

[FcoArturo]

Hello, need your help !

I managed to make a percetage of total sales : "% Venta" for each store (code_store).

This uses one table named SalesPOS

Then I wanted to add a lookup table to get the name of the store (Nombre_Local) , the relationship works perfectly.

 

But when i add the StoreName from the Lookup table (Locales), the precentage breaks and I get duplicated values  :

This is how the Metrics are calculated :

Overall Sales = CALCULATE( 
                            [Sales Amount],
                            ALL('SalesPOS (noOLD)'[code_store]
                            ))

Sales Amount = SUM('SalesPOS (noOLD)'[total])


% Venta = DIVIDE ( 'SalesPOS (noOLD)'[Sales Amount], 'SalesPOS (noOLD)'[Overall Sales],0)

Any ideas what AM I doing wrong ?

Thank you!

[v-chenwuz-msft · Accepted solution]

Hi @FcoArturo ,

Two methods:

1 Change the measure

Overall Sales = 
CALCULATE(
    [Sales Amount],
    ALLSELECTED('Locales')
)

2 Change the relationship's crossfilter direction to both.

Best Regards

Community Support Team _ chenwu zhu

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

[Ashish_Mathur]

Hi,

To your visual, ensure that the Code_Store field is also dragged from the Locales table.

[grantsamborn]

Try this change:

Overall Sales = 
CALCULATE(
    [Sales Amount],
    ALL( 'Locales' )
)