cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mypants Frequent Visitor
Frequent Visitor

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/Percentage-of-total-by-Product/td-p/154838

 

I have tried:

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

 

But, I keep getting the error:
Too few arguments were passed to the ALLEXCEPT function. The minimum argument count for the function is 2.

 

Image below - What I need is the percentage column by day (not all time)... (Excuse the datetime, I opened the data in excel and it created the timestamp, it is a date in PBI) 

 

 

things3.PNG

 

Thanks in advance, let me know if you require anymore context?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Need Help on total percent

Hi,

 

Try this

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Need Help on total percent

Hi,

 

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

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
mypants Frequent Visitor
Frequent Visitor

Re: Need Help on total percent

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

Super User
Super User

Re: Need Help on total percent

Hi,

 

Try this

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

mypants Frequent Visitor
Frequent Visitor

Re: Need Help on total percent

THANK YOU SO MUCH!

Super User
Super User

Re: Need Help on total percent

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors