topic Divide each single value of column 1 with a sum of values in column 2 for pie chart in DAX Commands and Tips

Divide each single value of column 1 with a sum of values in column 2 for pie chart

WinkieDink87 — Wed, 12 Jun 2024 02:45:41 GMT

Hi all. Using the data below, what I want to get is a percentage of the authorisedcount over the sum of receivedcount by country and month. So that in the pie chart when I create multiple slicers on Month and country (or other criterias) it will show the percentage of authcount/total receivecount depending on the selection.

For instance if we look at AU for May 2024, i would like to obtain the % value of 2141/8583 = 24.94% in the pie chart, in proportion to the other countries.

Thank you so much in advance and apologies as I am a begineer in PowerBI.

Re: Divide each single value of column 1 with a sum of values in column 2 for pie chart

rajendraongole1 — Wed, 12 Jun 2024 03:41:21 GMT

Hi WinkieDink87 -Create a measure based on the fields to calculate percentage of authorisedcount over the total receivedcount by country and month.

AuthPercentage =

DIVIDE(

SUM('Piechart'[authorisedcount]),

SUM('Piechart'[receivedcount]),

) * 100

Able to see as per selection for AU for may month 24.94%,check the snapshot fyr.I hope it works. please check

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Re: Divide each single value of column 1 with a sum of values in column 2 for pie chart

WinkieDink87 — Wed, 12 Jun 2024 06:56:28 GMT

Good day rajendraongole1 thank you so much for your reply and help! However it didnt seem to help. The calculation doesnt seem to change when I select a different month period in my slicer. And the calculation number doesnt tally.

Re: Divide each single value of column 1 with a sum of values in column 2 for pie chart

Anonymous — Mon, 17 Jun 2024 05:39:49 GMT

Hi rajendraongole1 ,

Thanks for the quick response.

Hi WinkieDink87 ,

Please have a try.

AuthPercentage = SUM ( Data[authorisedcount] ) / CALCULATE ( SUM ( Data[receivedcount] ), ALLEXCEPT ( Data, Data[Country], Data[Month] ) )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

Re: Divide each single value of column 1 with a sum of values in column 2 for pie chart

Ray_Minds — Mon, 17 Jun 2024 14:38:52 GMT

PercentageAuthorisedOverReceived =
DIVIDE(
SUM(Data[authorisedcount]),
CALCULATE(
SUM(Data[receivedcount]),
ALLEXCEPT(Data, Data[Country], Data[Month])
),
0
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Re: Divide each single value of column 1 with a sum of values in column 2 for pie chart

WinkieDink87 — Tue, 23 Jul 2024 01:12:04 GMT

Good day Anonymous and Ray_Minds ,

Thank you for your response! The formulas didnt help to calculate the data of authorized count/SUM of received count depending on the Month and country. A sample table is as below:

Auth Count	Rec Count	Country	Month
100	150	AU	Jan-24
200	250	NZ	Jan-24
300	350	SG	Jan-24
400	450	AU	Feb-24
500	550	NZ	Feb-24
600	650	SG	Feb-24

What I wish to get is :

For example if I select a slicer for AU, NZ and SG for Jan-24, I would like to see in a pie chart AU to reflect 100/(150+250+350)*100 = 13.33%. NZ to reflect 200/(150+250+350)*100 = 26.67% and SG to reflect 300/(150+250+350)*100 = 40%

I tried using the formulas provided but I am not getting the right calculation. May I ask if it could be something else I missed out?

Thank you so much!