cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Percent of group total

Hi,

 

Share some data and show the expected result.


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

Re: Percent of group total

Hello,

 

Below is a table of sample data:

 

 Sample data.JPG

 

 

 

 

 

 

 

 

 

 

 

Following the steps in this post, I created the following measures:

 

     %Activity = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Activity])))

 

     %Charge = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Charge])))

 

     %Employee = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Employee])))

 

     Level1 = IF([%Activity]=1, [%Charge], [%Activity])

 

     MeasureSelect = IF([Level1]=1, [%Employee], [Level1])

 

The resulting matrix is below:

 

:Sample matrix.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Note that where there is a single value at the lowest level (e.g. under Customer Charge), the % is calculating as the % of that value across all employees, rather than for the single employee.  Each one of the lines for Project A should show 100% as it is 100% of the values under Customer Charge.  Non-customer Charges - where there are more than one at the lowest value - are calculating correctly.

 

Do you have any idea why this is happening and how to fix it?

 

 

Thank you very much for any help you can provide.

 

- Stacey

 

 

 

 

Super User
Super User

Re: Percent of group total

Hi,

 

Based on the first table that you have shared, please share the exact result you are expecting.  I will help you with writing the DAX formulas.


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

Re: Percent of group total

Hello,

 

Here are the reults I want. 

 

 

Desired Results.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I appreciate any help you can give.

 

- Stacey

 

 

Super User
Super User

Re: Percent of group total

Hi,

 

Share the link from where i can download your PBI file.


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

Re: Percent of group total

Hello,

 

The file can be found here:  https://1drv.ms/u/s!AjomjdFc4LMegbsBv_xr6JTOJBcTmQ

 

Thank you,

 

-Stacey

Highlighted
Super User
Super User

Re: Percent of group total

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


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

Re: Percent of group total

Hello,

 

Thank you for the pbi file!  Curiously, why this works with my sample data, when I created corresponding 'Total hours' and 'Measure' in my actual pbi dataset it did not return the same results.  In this case, those with a single value at the lowest level correctly calculated to 100%, yet those with multiple values - which previously calculated correctly - now show erroneous %.  I cannot even determine what the % returned is representing.  I've uploaded the pbix here:  https://onedrive.live.com/?id=1EB3E05CD18D263A%2123936&cid=1EB3E05CD18D263A

 

Thank you,

 

- Stacey

Super User
Super User

Re: Percent of group total

Hi,

 

There is no file that link.


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

Re: Percent of group total

Hello,

 

My apologies.  Please try this link:  https://1drv.ms/u/s!AjomjdFc4LMegbsGbGiMocCsHwJR_w

 

Thank you,

 

- Stacey

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 250 members 2,668 guests
Please welcome our newest community members: