Forum Discussion
Creating a visual with measures
Hello guys,
In my table there is a category and quantity column, and I would like to show on a visual the share of each category from the total. I created measures for them which calculates the share, but when I try to create the visual, I add the category, then the share and it will show up like this, so it shows the share for each category:
Shares are calculated like this: first I created a measure for each category which calculates the total quantity for that category, also created a measure for the sum of the quantites, then created a measure for each of them to calculate the share from the two measures. Feel like I overcomplicated it, could you please assist me with what would be the best option for this?
Hi pter14 ,
1.Create a Single Measure for Category Share
Instead of creating individual measures for each category, you can use a single dynamic measure:
Category Share = DIVIDE( SUM('Table'[Quantity]), CALCULATE(SUM('Table'[Quantity]), ALL('Table'[Category])) )
This formula calculates the share of each category’s quantity out of the total quantity dynamically.2.Use a Stacked Column Chart
- Add Category to the Axis of the visual.
- Add the Category Share measure to the Values.
- Format the visual to display percentage values.
3. Add Tooltips (Optional)
To enhance the visual:
Include additional details in the tooltip, like the total quantity or the raw value for each category, using separate measures.
Please mark this as solution if it helps yopu. Appreciate Kudos.
5 Replies
pter14 , Try using
Create a measure for the total quantity:
TotalQuantity = SUM('YourTable'[Quantity])
Than Create a measure for the share of each category:
CategoryShare =
DIVIDE(
SUM('YourTable'[Quantity]),
[TotalQuantity],
0
)Create a bar chart visualization:
Add the Category column to the Axis field.
Add the CategoryShare measure to the Values field.Hello bhanu_gautam,
Thank you for a quick suggestion, unfortunately this did not work. It is giving 100% for all of the categories.
Hi pter14 ,
1.Create a Single Measure for Category Share
Instead of creating individual measures for each category, you can use a single dynamic measure:
Category Share = DIVIDE( SUM('Table'[Quantity]), CALCULATE(SUM('Table'[Quantity]), ALL('Table'[Category])) )
This formula calculates the share of each category’s quantity out of the total quantity dynamically.2.Use a Stacked Column Chart
- Add Category to the Axis of the visual.
- Add the Category Share measure to the Values.
- Format the visual to display percentage values.
3. Add Tooltips (Optional)
To enhance the visual:
Include additional details in the tooltip, like the total quantity or the raw value for each category, using separate measures.
Please mark this as solution if it helps yopu. Appreciate Kudos.
Hello FarhanJeelani,
Thank you for the quick solution, unfortunately this is not working for me.
Category Share = DIVIDE( SUM('Table'[Quantity]), CALCULATE(SUM('Table'[Quantity]), ALL('Table'[Category])) ) This formula gives back 100% for all of the categories, and I felt like the numerator and denominator are switched, so i tried:Category Share = DIVIDE(CALCULATE(SUM('Table'[Quantity]), ALL('Table'[Category])), SUM('Table'[Quantity]))
This 2nd one I have to divide by 100 and it gives me back percentages, but not the right percentages.
Maybe I am doing something wrong, or you need more clarficiation on the data?Dear FarhanJeelani,
Maybe a filter was on, or I dont know, but it looks like it is working now. Thank you for your assist. Could you please clarify what went wrong on my thinking?
