topic Re: How to calculate % of a count of a column? in DAX Commands and Tips

How to calculate % of a count of a column?

Anonymous — Thu, 23 Mar 2023 15:00:08 GMT

I'm trying to calculate the % of waste distribution type for every month year so for example in the above image, I want a measure to calculate the % of every month so it should be like for Jan 2021 the % of Closed loop recycling =( 7/(7+15+24))*100 and same for landfill and open loop recycling. I want to display the % of waste distribution in a line chart against the date to get a trend of how much is he % of waste distrbution for every type in every month and year.

How do I create a measure for this?

Re: How to calculate % of a count of a column?

WinterMist — Thu, 23 Mar 2023 20:56:37 GMT

Anonymous

I separated this into 2 measures so you can see the results, but feel free to combine them.

Hopefully this is helpful to you.

Regards,

Nathan

Re: How to calculate % of a count of a column?

Anonymous — Mon, 27 Mar 2023 16:14:55 GMT

I used the above formula to calculate month total and it's not giving the correct inforation and when I bring it to the visualisation it's not displaying any info.

Re: How to calculate % of a count of a column?

WinterMist — Mon, 27 Mar 2023 17:21:13 GMT

Anonymous

Whenever this happens to me, it's helpful to separate each VAR (and the RETURN section) into its own measure to isolate smaller pieces of code.

If the at-fault piece of code is a CALCULATE statement, try changing it a bit to see if the issue is with the FILTER section or something else. In this way, you should be able to identify the exact line of code that's causing the problem.

Once you can pinpoint which piece of code is causing the visual to break, then you need to click "See Details" to see what the error message is.

Regards,

Nathan

Re: How to calculate % of a count of a column?

Anonymous — Mon, 27 Mar 2023 17:58:12 GMT

I tried the formula you have provided and I'm getting an error in the Count of waste distribution type in the 1st image as you can see. I've added the screenshot for the count of waste distribution type measure which I have created. Could you please help with what's going wrong?

Re: How to calculate % of a count of a column?

WinterMist — Mon, 27 Mar 2023 18:24:53 GMT

I did not have access to your dataset, so I simply created a table based on your screenshot from the original post.

In my case, [Count of Waste Distribution Type] is actually a column in the table.

But you don't have this column, which is the reason for the error.

In your case, it's the measure.

Try replacing the SUM clause with your measure as follows:

CALCULATE(

[Count of Waste distribution type],

ALL('Waste data (3)'),

'Calendar'[Date] = GivenDate

)

If it's still not working, perhaps you can share the PBIX on Google Drive and share the link here.

Regards,

Nathan

Re: How to calculate % of a count of a column?

Anonymous — Mon, 27 Mar 2023 19:14:08 GMT

The new replaced SUM clause worked for me. Can you give me the new % measure as well?

Re: How to calculate % of a count of a column?

Anonymous — Mon, 27 Mar 2023 19:31:07 GMT

I created the % measure and it worked. Thank you so much 🙂 I had been stuck on this for more than 2 weeks. Thank you 🙂