How to calculate sum of multiple similar values based on months

[nishad1092]

Hi, 

I have values coming from Multiple same Months, 
How to calculate the sum of December, January, February seperately:

Also, there is a Name column with multiple values, For example: abc, xyy. So Even if I select multiple values, abc and xyz from  the slicer for the January month, The total value should be = Sum of all Jan of abc + Sum of all feb xyz.

[Ahmedx]

pls see my video

https://1drv.ms/v/s!AiUZ0Ws7G26RiUv51TeoBXDhfBNg?e=BP50XH

[v-jingzhang]

Hi @nishad1092 

Your current "Diff Value Max Id CM - PM" column is of Text type with " kWh", so it cannot be used to calculate the sum. You can add a new column from it to extract only the numbers and change this new column to number type. A number type column can be summed. 

Diff Value = VALUE(LEFT([Diff Value Max Id CM - PM],LEN([Diff Value Max Id CM - PM])-4))

Then use the new column in the table visual and select "Sum" aggregation type for it. 

Notice that you need to create a relationship between the name table and the data table on Name column, thus you can use the slicer to filter the sum results. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Ritaf1983]

Hi @nishad1092 
This is the way the standard sum function works including slicing by slicer selections.
If I missing something 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[nishad1092]

Hi @Ritaf1983 

Thank you for the support !
I'll share two files, One with the sample data and other with the options for the slicer here:

Sample Data:
https://drive.google.com/file/d/1nHU5jecaHeEhbuC6CsG5WGJ4ZucfF8Cy/view?usp=sharing

Slicer Data:
https://drive.google.com/file/d/1gei1Kv7AwzewaJqRyVllP5gM4olC91R4/view?usp=sharing

Now the expected data would be:

If I select tag, FCN2!PGM.RS01.PM01001.SUM.HMI_SUM.SUM(1) For January, 2023, It has to return Sum as total of these two values, i.e : 1312 + 2188 = 3500 

I hope I'm clear. Please let me know if you require more clarity.

[nishad1092]

HI @Ritaf1983 

Were you able to access the dataset.

[nishad1092]

@Ritaf1983 
Hi, 
Were you able to look into it ? Please let me know.

[Ritaf1983]

Hi @nishad1092 
I see that @v-jingzhang  answered you including a sample file with the solution...

[nishad1092]

@v-jingzhang 
Please could you post the solution again.

[nishad1092]

Hi @v-jingzhang 

I can see the mail of the comment, 
I'm trying the solution but facing the error from the LEFT function as the other value is of non-number type:

[v-jingzhang]

Hi @nishad1092 

Is [Diff Value Max Id CM - PM] a measure? I don't see it in the table you showed in the screenshot. 

[nishad1092]

@v-jingzhang 

Yes, its a measure.

[v-jingzhang]

If it's a measure, my old solution may not work as I was assuming that it was a column. Can you show the formula of the measure? We may need to create a new measure to get the expected result. 

[nishad1092]

Hi @v-jingzhang 

Okay, I'm sharing the link to the .pbix file, You will find all the measures here.

https://drive.google.com/file/d/1X1RRa3DmSEEuRi36_TE-LFtSei4qpGTL/view?usp=sharing

Please refer to this. 

Yes, a new measure is required to find the Total of Sum of Value per Month wise.

[v-jingzhang]

Hi @nishad1092 

Is this what you want?

Diff Value Max Id CM - PM 2 = SUMX(VALUES(Sheet1[YearMonth]),[Value Max Id Last Day CM] - [Value Max Id Last Day PM])

Best Regards,
Jing

[nishad1092]

Hi @v-jingzhang 

Thank you for all your replies.

I tried this measure but it didn't give me the results as expected.

The previous solution was correct but then it is not a column I wanted, so can't use it. 

The expected result should be, exactly this:

[nishad1092]

Hi @v-jingzhang 

Could you check this once please. 

It should show sum of (Diff Value Max id CM -PM) against each month when all Name tags are selected..

[nishad1092]

Hi @v-jingzhang 

Could you check this again please ?

[nishad1092]

Hi @Ritaf1983 

Looks like there's no response from anyone. 
@v-jingzhang Is almost there with the results, But need little alteration. 

Please kindly support me. 

[nishad1092]

Hi @lucadelicio 

Hi, Could you support me in this please. As you are familiar with the problem, maybe you could support here. 
Sorry, But I have taken the freedom to pull you here.

[nishad1092]

Hi @Ritaf1983 

I think it has been removed before I could check it out. 
I'm not able to see that comment.