Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DanJBay1978
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

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

View solution in original post

6 REPLIES 6
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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

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.

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

matteocarnelos
Frequent Visitor

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

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.