% of total by category by year

[parry2k]

Hello all,

it seems to be very easy but somehow I'm unable to figure out.

I have data in following format where invoice amount is for whole month. I want to calculate invoice amount fo each category by

Category share =  total minutes of category / (sum of total minutes of category by year and month)

Category Amount = invoice amount * Category Share

Here is sample data

Year   Month  Category   Total Minutes   Invoice Amount   

2015    1         A                1000               20000

2015    1         B                2000               20000

2015    2         A                1000               10000

2015    2         B                2000               10000

2015    2         C                2000               10000

Here is the end result

Year   Month  Category   Total Minutes   Invoice Amount      Category Share    Category Amount

2015    1         A                1000               20000                      0.33                               6666.67

2015    1         B                2000               20000                      0.67                             13333.33 

2015    2         A                1000               10000                      0.17                               1666.67

2015    2         B                2000               10000                      0.33                               3333.33

2015    2         C                2000               10000                      0.50                               5000.00

Help please!

Thanks,

Parvinder

[Haegi]

Hi,

It's possible to use Measure column and DAX language.

To calculate the cumulative value "total minutes of category" you can use this formula below

http://www.daxpatterns.com/cumulative-total/

Example (personal)

Cumulative = CALCULATE(SUM(mAggregate[aggAmount]); FILTER(ALL(dDates[Date]); dDates[Date] <= MAX(dDates[Date])))

The value of measure is recalculated normaly for each Category.

You can test with a matrix tab.

After you can create another measure like this

CategoryShare = TotalMinute/Cumulative

For the last column i don't know if a measure if necessary or if a calculated column is enought.

Hope it will help you.

Regards.

[parry2k · Accepted solution]

I tried the solution and created a new measure and put it in matrix which matches the total calculated by matrix. But challenge here is when I'm filtering on a category it is applying that filter on new calculated measure field which I don't want. 

Not sure if I'm doing something wrong here.

Thanks,

[parry2k]

Seems like  I figure it out, will keep posted here after solution worked for me.

[Haegi]

Hi,

Normaly a measure depend to a particular context .

But it's work properly with any filters if the column in measure and the filter provide of the same table i thinks or distinct tables if it existist a relation between.

Regards.