Percentage of total

[DanJBay1978]

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.

[v-piga-msft]

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.

Hope this can help you! 

You also could have a view of the attachment.

Best Regards,

Cherry

[DanJBay1978 · 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

[v-piga-msft]

Hi @DanJBay1978,

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

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

Hope this can help you!

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

Best Regards,

Cherry

[DanJBay1978]

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]

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]

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