Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sarikei
Helper I
Helper I

Sum Total

Hi,
In my report, I have a Cluster Column Chart to display the last 12 months of Sales Volumn.
So I can see the Sales Volumn of each month.
How can I create the measure to sum all the Sales Volumn of these 12 months, then use it to calculate the percentage of sales in each Month?

 

Many thanks.

1 ACCEPTED SOLUTION

Hi @sarikei ,

 

Please create following measures:

Sales = SUM('Table'[Sales Volumn])

TotalSales = CALCULATE(SUM('Table'[Sales Volumn]),ALL('Table'))

% = DIVIDE([Sales],[TotalSales])

 

The result you want:

vyadongfmsft_0-1669974313734.png

Best regards,

Yadong Fang

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

View solution in original post

7 REPLIES 7
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @sarikei ,

 

without seeing your data the solution would typically look like this or similar:

 

ShareOfYear =

var var_CurrentValue = [YourMeasure]
var var_ValueofYear = CALCULATE([YourMeasure],ALL(DateTable[MonthColumn])
var var_ShareofCurrentValue = DIVIDE(var_CurrentValue,var_ValueofYear)

Return
var_ShareofCurrentValue

 

In best case you have a separate date table. It is important that you put for [MonthColumn] the column you use in your visual. 

 

If you also apply a quarter column then you need to add ",ALL(DateTable[QuarterColumn])" behing the other ALL() function. If there are more columns to be ignored also ALLEXCEPT() instead of ALL() could help.

 

If this does not work please share the data model, your measure, the visual as well as filter applied to the visual (sample data is totally fine) then it is easier to help.

 

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi,

 

sorry, I should have included the dataset. For example:

 

MonthSales VolumnTotal Sales Volumn% Sales Volumn
2021-10226224810.05%
2021-11281224812.50%
2021-1215022486.67%
2022-0112322485.47%
2022-0220822489.25%
2022-03226224810.05%
2022-0415722486.98%
2022-0521822489.70%
2022-0614022486.23%
2022-0717122487.61%
2022-0822022489.79%
2022-0912822485.69%

 

1. Total Sales Volumn is what I want to sum. This is the total of Sales Volumn of all the all Month.

2. Then I will use this measure (Total Sales Volumn) to calculate the % Sales Volumn (fomular is Sales Volumn / Total Sales Volumn).

3. So I will include this 3 measure in the Column Chart.

 

Thank you in advance.

Hi @sarikei 

Please use the following measure:

ShareOfYear =

var var_CurrentValue = SUM(Table[Sales Volumn])
var var_ValueofYear = CALCULATE(SUM(Table[Sales Volumn]),ALL(Table[Month])
var var_ShareofCurrentValue = DIVIDE(var_CurrentValue,var_ValueofYear)

Return
var_ShareofCurrentValue

 

But first you have to split the Month Column in Power Query (see split by delimeter function) or additionally create a month and a year column so that you can use the initial column for other purposes. But in the end you should have a separate year column and a separate month column.

 

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi @sarikei ,

 

sorry I have not read everything completely I think. So if want percentage of all rows then you should go with the solution of @v-yadongf-msft .

 

If you only want to have the current year then you can use my solution.

 

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi @sarikei ,

 

Please create following measures:

Sales = SUM('Table'[Sales Volumn])

TotalSales = CALCULATE(SUM('Table'[Sales Volumn]),ALL('Table'))

% = DIVIDE([Sales],[TotalSales])

 

The result you want:

vyadongfmsft_0-1669974313734.png

Best regards,

Yadong Fang

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

@v-yadongf-msft  i have one doubt if i am select any particular Date is ther any possible to change total sales values based  sales ?

vanessafvg
Super User
Super User

are you able to share some of your data in text form or show what you data looks like.  In order ot provide date intelligence you need to link your transactional date to a continious date table.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.