Calculate percentage based on several categories

[PXJ99]

Hi all,

Been trying really hard to get the percentage (file in the link, unable to copy it here as an error pops up). Percentage of the amount based on DM, KIC, currency and year

2019-20

NSW local = Local divide (LocalplusOveseas)
NSW VIC= Local divide (Local plus Oveseas)

For each year and then by DM (ABC and XYZ), is that possibe please?

I would appreacite your help. Thanks very much.

https://www.dropbox.com/scl/fi/0at9lkdi7yq1p03jo3cya/percentage-by-cat.xlsx?dl=0&rlkey=u2ctinq7xhw6w...

[PXJ99]

Thanks. Is there a way to do it if the year was not a fiscal year and just 2020 instead of 2019-20

  2020                                     2021                             2022

NSW Local 40%                NSW Local 70%              NSW Local 20%
NSW Overseas 60%          NSW Overseas 30%       NSW Overseas  80%

VIC Local 45%                   VIC Local 30%              VIC Local 10%

VIC Overseas 55%             VIC Overseas 70%        VIC Overseas 90%

I changed the database to a calendar year. Let me know if its possible.

https://www.dropbox.com/scl/fi/e0ogrflvra64shh06qu0t/percentage-by-cat2.xlsx?dl=0&rlkey=grmwzdzde0y8...

[Kishore_KVN]

Hello @PXJ99 , 

you can create a key column as per your requirement and here I am combining DM and year and the DAX for it looks like this:

Key = 'Table'[DM]&'Table'[Year]

Then create measures for NSW and VIC like this:

NSW Local = 
Var Num = CALCULATE(SUM('Table'[NSW]),FILTER('Table','Table'[Currency] = "Local"),FILTER('Table','Table'[KIC] = "NSW"),ALLEXCEPT('Table','Table'[Key]))
Var Den = CALCULATE(SUM('Table'[NSW]),FILTER('Table','Table'[KIC] = "NSW"),ALLEXCEPT('Table','Table'[Key]))
Var Result = DIVIDE(Num,Den,0)
Return
Result

VIC Local = 
Var Num = CALCULATE(SUM('Table'[NSW]),FILTER('Table','Table'[Currency] = "Local"),FILTER('Table','Table'[KIC] = "VIC"),ALLEXCEPT('Table','Table'[Key]))
Var Den = CALCULATE(SUM('Table'[NSW]),FILTER('Table','Table'[KIC] = "VIC"),ALLEXCEPT('Table','Table'[Key]))
Var Result = DIVIDE(Num,Den,0)
Return
Result

Output looks like this:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[PXJ99]

Hi Kishore,

Thanks for the measure. 

I am actually trying to create the percentage as follows:

    2020-21                               2021-22                              2022-23

NSW Local 40%                NSW Local 70%              NSW Local 20%
NSW Overseas 60%          NSW Overseas 30%       NSW Overseas  80%

VIC Local 45%                   VIC Local 30%              VIC Local 10%

VIC Overseas 55%             VIC Overseas 70%        VIC Overseas 90%

So I can plot it as a table and a pie visual.

I've got the pbix file with the data and your measure:

https://www.dropbox.com/s/ib5j7kwhcwlx31j/Percentage%20based%20on%20several%20categories.pbix?dl=0

Any help from you would be greatly appreciated.
Thanks

[Kishore_KVN]

Hello @PXJ99 ,

Without months you can not create fiscal year calculation like 2019-2020 can be created only if there are months like April 2019 to march 2020. So you may need to redesign your database.