percentage by category

[Anonymous]

Hi,

I am using a simple table visual (direct query mode to a tabular model) and I want to display the percent values of a measure relative to a category subtotal (in my case it's the delivery number and Material:

Here's is an example of the data i have.

Delivery	Material	Quantity
1	abc	20
2	abc	5
2	def	5
3	xyz	10

Now I want to add a column with the Qunatity percentages of Material by Delivery.

Delivery 1 Quantity 20 = 100% of Delivery Subtotal

Delivery 2 Quantity 5 for material abc is = 50% of Delivery Subtotal

Delivery 2 Quantity 5 for material def is = 50% of Delivery Subtotal

Delivery 3 Quantity 10 for material xyz is = 100% of Delivery Subtotal

Should look like this with Column Subtotal Percentag as desired outcome.

Delivery	Material	Quantity	Subtotal Percentage
1	abc	20	100%
2	abc	5	50%
2	def	5	50%
3	xyz	10	100%

In the context menue of my table metric I can only chose: "Percentage of Grand Total" but that doesn't do the job.

Should be fairly simpel but I didn't find anything that solves it....

Any suggestions?

Thank you very much.

Lars

[FrankAT · Accepted solution]

Hi @Anonymous ,

take a look at the following solution:

 Regards FrankAT

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Create a measure as below:

Measure = 
var _counttoal=COUNTX(FILTER(ALL('Table'),'Table'[Delivery]=MAX('Table'[Delivery])),'Table'[Delivery])
var _countsub=COUNTX(FILTER(ALL('Table'),'Table'[Delivery]=MAX('Table'[Delivery])&&'Table'[Material]=MAX('Table'[Material])),'Table'[Delivery])
Return
DIVIDE(_countsub,_counttoal)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[FrankAT · Accepted solution]

Hi @Anonymous ,

take a look at the following solution:

 Regards FrankAT

[Greg_Deckler]

@Anonymous - Sorry, not 100% following. Is the data source data, expected output? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.