topic Re: Measure to Calculate Percentage in DAX Commands and Tips

Measure to Calculate Percentage

ShaGuna32 — Wed, 20 Sep 2023 23:19:03 GMT

Hi Team,

Good day, all,

I am trying to calculate a percentage of data using a measure in the matrix table format.

Vendor Name the Row, In the values, I have M category and M %(this is the field I am trying to work)

While I am trying this in the matrix table option in the report,
I couldn't able to bring the M category total value divided by the particular M Category. Because the same M category is used in the column, the total value is categorized in the M % calculation. It is stopping me from pursuing further.

Any ideas are highly appreciated, thanks.

Thanks
ShaGuna

Re: Measure to Calculate Percentage

Anonymous — Fri, 22 Sep 2023 04:55:14 GMT

Hi ShaGuna32 ,

You can create a measure as below to get the M%:

Measure = VAR _per = SUM ( 'Table'[M Category] ) VAR _total = CALCULATE ( SUM ( 'Table'[M Category] ), ALLEXCEPT ( 'Table', 'Table'[Vendor] ) ) RETURN DIVIDE ( _per, _total )

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples.It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Measure to Calculate Percentage

ShaGuna32 — Sun, 24 Sep 2023 23:59:06 GMT

Hi Anonymous ,

Good day,

Thanks for your reply, much appreciated.
First of all, I need to count the M for the total in each M category for the vendors,
Then I want to Divide the count of M by the total of the M category for the vendor(with out excluding the vendor or that particular count of M).

Hope I am not confusing you, Please do reach out if need any clarifications.
Looking forward to your suggestion, thanks.

Have a nice day,

kind Regards
ShaGuna

Re: Measure to Calculate Percentage

Anonymous — Mon, 25 Sep 2023 02:06:08 GMT

Hi ShaGuna32 ,

Please update the formula of measure as below and check if it can get your expected result.

Measure = VAR _count = COUNT ( 'Table'[M Category] ) VAR _total = CALCULATE ( SUM ( 'Table'[M Category] ), ALLEXCEPT ( 'Table', 'Table'[Vendor] ) ) RETURN DIVIDE ( _total, _count )

If the above one still can't help you get your expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

Best Regards

Re: Measure to Calculate Percentage

ShaGuna32 — Mon, 25 Sep 2023 04:35:26 GMT

Hi Anonymous ,

Unfortunately not.

It is dividing the same value and returns 1.

Thanks
ShaGuna

Re: Measure to Calculate Percentage

Anonymous — Mon, 25 Sep 2023 04:38:35 GMT

Hi ShaGuna32 ，

Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

Best Regards

Re: Measure to Calculate Percentage

ShaGuna32 — Mon, 25 Sep 2023 05:32:19 GMT

Hi Anonymous ,

Sure can,

I am trying to calculate the Material Quality percentage of our suppliers. We have a ranking from 0 to 12 for each material. Each and every material will be ranked according to their supply quality. So every vendor's individual product will be ranked at the end of the process.

Dummy Data (Raw Model),

Vendor	Product Rack	Material Quality Grade
Vendor 1	R1 (random generated number )	5
Vendor 2	R 2	6
Vendor 3	R 3	3
Vendor 4	R 4	1
Vendor 5	R 5	7
Vendor 6	R 6	4
Vendor 7	R 7	9
Vendor 8	R 8	10
Vendor 9	R 9	2

The team using V-LookUp at the data in such a way, That will easily allow them to categorize by the number of products in each ranking Category by counting the number ranks for each vendor product

What I want to do in Power BI is the following,

Unfortunately, I am not sure how to use the total Count of each Rank divided by the Grand Total.
I have done an SQL script to have the Vendor, Product Rack, and Material Quality Grade in the same table.
Hope this gives you some understanding, much appreciate for your time and help.

Thanks
ShaGuna

Re: Measure to Calculate Percentage

Anonymous — Mon, 25 Sep 2023 06:20:47 GMT

Hi ShaGuna32 ,

I created a sample pbix file(see the attachment) base on your provided data, please find the details in it. You can create a measure as below to get it:

M % = VAR _vendor = SELECTEDVALUE ( 'Table'[Vendor] ) VAR _per = COUNT ( 'Table'[Product Rack] ) VAR _total = CALCULATE ( COUNT ( 'Table'[Product Rack] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Vendor] = _vendor ) ) RETURN DIVIDE ( _per, _total )

Best Regards

Re: Measure to Calculate Percentage

ShaGuna32 — Mon, 25 Sep 2023 22:39:04 GMT

HI Anonymous ,

Much appreciated for your support in this issue, thank you so much,
It worked well, Thanks.

Kind Regards

ShaGuna

Re: Measure to Calculate Percentage

ShaGuna32 — Wed, 11 Oct 2023 06:09:40 GMT

Hi Anonymous and Everyone

Thanks for the below solution Anonymous

I have achieved results as follows,

Now,
I would like to use the Grand Total average values in the empty cells in the column, or the values above or below in the column.

I tried to use MAX, Earlier functions but they are used only if I have a calculated column. I have created a measure for the previously suggested formula. Tried to create a calculated column but isn't working correctly.

Any ideas for the same will be highly appreciated,
Looking forward to any ideas and suggestions, thanks.

Kind Regards
ShaGuna