Total Sales % Card based on Slicer Selection

[ksharpes]

Hi All, 

just started using Power BI so apologies if this is a basic question!

I have created a Card with the following formula to show a percentage of sales vs total Sales, but I would like to show the percentage based on a slicer selection.

Current Formula is:

Total Cogs % = divide(sum('EPOS'[Dispatched COGS]),calculate(sum('EPOS'[Dispatched COGS]), all()))

I would like the Total calculation "calculate(sum('EPOS'[Dispatched COGS]), all()" to be a total based on a Year slicer "EPOS'[Year]"

Many thanks for your help.

[ksharpes · Accepted solution]

Thanks for the help, I have figured it out:

Total Cogs % = DIVIDE(

    SUM('Amazon Week EPOS'[Dispatched COGS]),

    CALCULATE(SUM('Amazon Week EPOS'[Dispatched COGS]), ALLEXCEPT('Amazon Week EPOS','Amazon Week EPOS'[Week End].[Year]))

)

[v-yajiewan-msft]

Hi @ksharpes , aduguid, thank you for your prompt reply!

Use the ALLSELECTED function to get the year in slicer:

Total Cogs % = DIVIDE(
    SUM('EPOS'[Dispatched COGS]),
    CALCULATE(SUM('EPOS'[Dispatched COGS]), ALLSELECTED('EPOS'[Year]))
)

 If the code still doesn't work, please provide more example data (including tables, relationship, etc.) for better testing.

Best regards,

Joyce

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

[aduguid]

Try this measure 

Total Cogs % = 
DIVIDE(
    SUM('EPOS'[Dispatched COGS]), 
    CALCULATE(
        SUM('EPOS'[Dispatched COGS]),
        REMOVEFILTERS('EPOS'[Year])
    )
)

[ksharpes]

Doesnt seem to work, I want to include the selection from the Year slicer.

I currently have 2 values for Year 2023 or 2024

so in theory the sum would be Dispatched Cogs (2023) / Total Dispatched COGS (2023)

[aduguid]

In that case try these measures 

Dispatched Cogs = 
CALCULATE(
    SUM('YourTable'[Dispatched Cogs]),
    'YourTable'[Year] = SELECTEDVALUE('Year'[Year])
)

Total Dispatched COGS = 
CALCULATE(
    SUM('YourTable'[Total Dispatched COGS]),
    'YourTable'[Year] = SELECTEDVALUE('Year'[Year])
)

Dispatched Cogs Ratio = 
DIVIDE(
    [Dispatched Cogs],
    [Total Dispatched COGS]
)

[ksharpes]

Sorry still not working, I may have confused the issue slightly, for the [Total Dispatched COGS] I was using the calculation 

"calculate(sum('Amazon Week EPOS'[Dispatched COGS]), all())"

can the "Selectedvalue" art be included in the above?

"SELECTEDVALUE('Amazon Week EPOS'[Week End].[Year]) )"