Forum Discussion

mypants's avatar
mypants
Regular Visitor
8 years ago
Solved

Need Help on total percent

Hey All,    I need help on creating a percentage by date, as you know grand total does a percentage based on all data elements.   I tried the following: https://community.powerbi.com/t5/Desktop/P...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this

     

    =SUM('RPT DeliveryProjection'[TotalDeliveries]) / CALCULATE(SUM('RPT DeliveryProjection'[TotalDeliveries]),ALLEXCEPT('RPT DeliveryProjection','RPT DeliveryProjection'[WeekStarting]))

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

The ALLEXCEPT function takes the first input as a Table.  So the ALLEXCEPT portion should be:

 

ALLEXCEPT('RPT DeliveryProjection',['RPT DeliveryProjection'[WeekStarting])

mypants's avatar
mypants
Regular Visitor
8 years ago

Hey thanks, that raises the second error I get:

 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

The whole query:

 

Test = SUM('RPT DeliveryProjection'[TotalDeliveries]) / CALCULATE(SUM('RPT DeliveryProjection'[TotalDeliveries]),FILTER('RPT DeliveryProjection',ALLEXCEPT('RPT DeliveryProjection','RPT DeliveryProjection'[WeekStarting])))

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Try this

     

    =SUM('RPT DeliveryProjection'[TotalDeliveries]) / CALCULATE(SUM('RPT DeliveryProjection'[TotalDeliveries]),ALLEXCEPT('RPT DeliveryProjection','RPT DeliveryProjection'[WeekStarting]))

    • mypants's avatar
      mypants
      Regular Visitor
      8 years ago

      THANK YOU SO MUCH!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        You are welcome.