Forum Discussion

ksharpes's avatar
ksharpes
New Member
2 years ago
Solved

Total Sales % Card based on Slicer Selection

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.
Need Help
  • ksharpes's avatar
    ksharpes
    2 years ago

    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]))
    )

6 Replies

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Try this measure 

    Total Cogs % = 
DIVIDE(
    SUM('EPOS'[Dispatched COGS]), 
    CALCULATE(
        SUM('EPOS'[Dispatched COGS]),
        REMOVEFILTERS('EPOS'[Year])
    )
)
    • ksharpes's avatar
      ksharpes
      New Member
      2 years ago

      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's avatar
        aduguid
        Icon for Memorable Member rankMemorable Member
        2 years ago

        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]
)

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • ksharpes's avatar
    ksharpes
    New Member
    2 years ago

    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]))
    )