Divide the sum of a column member by the total of all columns

[johnsmith92]

Hi all,

I am new to PowerBI (coming from Tableau) and I am struggling with creating a DAX measure.

So, first off, my data comes from a tabluar cube and a huuuuuge model and unforunately I can't share it, but I'll do my best to describe. My Values come from a huge table with records and are already summed in the model before loading to PowerBI. My Months come from the standard Date table in the model.

Here's what I'm trying to replicate in PowerBI (the last row):

Basically, this calculates the total share of each month for the last 4 years.

My target visual in PowerBI is this:

I have tried a lot of things, but the only thing that worked is that I managed to replicate the Excel table (with % share instead of values) and it was dependent on the years included in the matrix (which I don't need). What I'm looking for is the last row with the blue text.

I tried to recreate the data in a pbix file: PBIX file 

Can you guys help me with this one?

[johnsmith92]

I can get the value only for one month, as seen here:

Spoiler

Understandibly it doesn't work:

  

Not sure if that's possible, but I want the above "Sum Months" calculation to replicate itself, depending which row is on the first column and replace the month part.

[amitchandak]

@johnsmith92 , you file is not opening, can you share the formula

[johnsmith92]

Could it be because I'm on January 2021 version?

[amitchandak]

@johnsmith92 , I am not Feb 2021, so it should open. Please share formula/measures, I will try to check

[johnsmith92]

Have you been able to open the file, sir?

[v-xiaoyan-msft · Accepted solution]

Hi@johnsmith92,

please try measure like this :

Measure = 
var a=
CALCULATE(
    SUM('Values'[Values]),
    FILTER(
        'Values',
        MONTH('Values'[Date])=MONTH(MAX('Values'[Date]))
    ))
var b=
CALCULATE(
SUM('Values'[Values]),ALL('Values'))
return
IF(
    HASONEVALUE('Date'[MonthName]),
    a/b,
    1
)

then you will get :

Here is the demo ,please try it : https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EQDTU6PV8wRKvuQOxRT... 

Hope it helps.

Best Regards,
Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[johnsmith92]

That works perfectly! Thank you so much!

[johnsmith92]

Hi @amitchandak . There's no formulas for the data, I have just put it in PowerBI manually. Can you try this again? New PBIX file