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

Percentage of total

Hi,

 

I'm new to Power BI and could use a little assistance with as issue i am facing.

 

I need to calculate a catergory % of the total for each financial year - I've enclosed a copy of some dummy data and the desired outcome I am hoping to achieve.

 

Any Help would be much appreciated.% of total.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
DanJBay1978 Frequent Visitor
Frequent Visitor

Re: Percentage of total

https://1drv.ms/u/s!AllwuDKdtsFdav5RTPt3M_v-M28

 

Thanks for your help - Cherry , I tried to replicate in my data model and could not achieve the same result for the 'Total Apps' Measure.

 

I've enclosed a link to a test PBIX file with dummy data and model- hopefully you can access.

 

Can you assist as to why I dont get the same result?

 

On a side issue - is there an option to just upload a PBIX file?

 

Thanks

Dan

6 REPLIES 6
matteocarnelos Frequent Visitor
Frequent Visitor

Re: Percentage of total

Hi Dan,

 

you can use the visualization "Matrix".

 

Watch this video to understand how use it

https://www.youtube.com/watch?v=pjhwoYu76ow

 

After that, you can modify the type of the value to obtain the percentage.

 

Is it what do you want?

 

Bye,

Matteo

DanJBay1978 Frequent Visitor
Frequent Visitor

Re: Percentage of total

Thanks for your help.

 

Unfortunatly this is not what I need.  I know how to the put the data into the matrix visualisation - i just dont know the correct DAX to create a total column see the orange column e.g the sum of apps for 2016 = 12, then I need the DAX calculation ( to calculate the green column) that says of the 2 sales for duration <90 days this is 2  divided by 12 for the year = 16.67 %.

 

Any help would be greatly appreciated.

 

Thanks

Dan

 

 

Community Support Team
Community Support Team

Re: Percentage of total

Hi @DanJBay1978,

 

You could create the measure and calculated column like below.

 

1. Create the measure to calculate Total apps for year;

 

Total apps for year =
CALCULATE (
    SUM ( 'Table'[app(FactSales)] ),
    FILTER (
        ALL ( 'Table' ),
        YEAR ( 'Table'[Year(DimDate)] ) = YEAR ( MAX ( 'Table'[Year(DimDate)] ) )
    )
)

2. Create the calculated column by DIVIDE function;

 

% =
DIVIDE ( 'Table'[Sales(Factsales)], [Total apps for year] )

Then create the matrix like below.

Capture.PNG

 

Hope this can help you! 

 

You also could have a view of the attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DanJBay1978 Frequent Visitor
Frequent Visitor

Re: Percentage of total

https://1drv.ms/u/s!AllwuDKdtsFdav5RTPt3M_v-M28

 

Thanks for your help - Cherry , I tried to replicate in my data model and could not achieve the same result for the 'Total Apps' Measure.

 

I've enclosed a link to a test PBIX file with dummy data and model- hopefully you can access.

 

Can you assist as to why I dont get the same result?

 

On a side issue - is there an option to just upload a PBIX file?

 

Thanks

Dan

Community Support Team
Community Support Team

Re: Percentage of total

Hi @DanJBay1978,

 

For your case, you could create this measure which should be more easier.

 

Measure = 
CALCULATE (
    SUM ( FactSales[Apps] ),
   ALL(FactSales[DurationGroup]
))

Capture.PNG

Hope this can help you!Smiley Very Happy

 

Besides, someone may have the option of attachment, some one may not. You could check if you have this option.

 

attachment.PNG

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DanJBay1978 Frequent Visitor
Frequent Visitor

Re: Percentage of total

Thank your help - much appreciated, this works perfectly.

 

As note - maybe just me - when I replicated the same process in the live environment and created the duration group as a calculated column at a report level - the above DAX did not work. Only seems to work when the duration group columns is created at database level then uploaded to the report.

 

Thanks again

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 43 members 1,289 guests
Please welcome our newest community members: