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/
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

Highlighted
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 (Last 30 Days)