Forum Discussion
Use value to calculate another value
I'm trying to do all of my calculations in PowerBI rather than having to dump it to Excel and do my calculations.
I need to calculate a percent of a total. The base number (denominator) is the where the Feature is called NULL. The description tells what the base item is. In the example below, the base is Apples with qty of 20 and Grapes with qty of 50.
What I want to calculate is the percent of that number for all of the other features, like seed/seedless and down another level to color of the apple or grape.
| Feature | Description | Qty | Percent of total |
| NULL | APPLES | 20 | 100% |
| SEED TYPE | SEEDS | 7 | 35% |
| COLOR | RED | 4 | 20% |
| COLOR | GREEN | 3 | 15% |
| SEED TYPE | SEEDLESS | 13 | 65% |
| COLOR | RED | 8 | 40% |
| COLOR | GREEN | 5 | 25% |
| NULL | GRAPES | 50 | 100% |
| SEED TYPE | SEEDS | 35 | 70% |
| COLOR | RED | 30 | 60% |
| COLOR | GREEN | 5 | 10% |
| SEED TYPE | SEEDLESS | 15 | 30% |
| COLOR | RED | 12 | 24% |
| COLOR | GREEN | 3 | 6% |
I can get the calculation to work at the first level, but cannot get it to go down to the subsequent levels. For example, I created "Measure1" that sums the qty where the feature = "NULL". Then I created "Measure2" = Qty / "Measure1". This gives me the "percent of total" = 100%, 35% for "SEED TYPE = SEEDS" and 65% for "SEED TYPE = SEEDLESS". This is as far as the calculation will go. How do I get it to calculate all the way down to the color as well?
2 Replies
You may take a look at the following post.
https://community.powerbi.com/t5/Desktop/Subtotal-Percentages/m-p/313658#M139139
This is close, but not fully what I'm looking for.
In my example, there are a total of 50 GRAPES. 12 of those grapes are RED SEEDLESS GRAPES. This is 24% of the total of 50 GRAPES. How would you have Power BI calculate that?
