Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to do something that is way out of my league, yet should I accomplish it, the output would be really powerful. It might be a trivial solution, but I might not be able to describe good enough what I am trying to do, but I will give it a shot.
In the visual table below each 0 indicates a Store with no sales of a Product Category. It is filtered, so the hundreds of other Store with sales in both Alfa and Bravo are not displayed.
Product Category | ||
Store | Alfa | Bravo |
Store 1 | 0 | |
Store 2 | 0 | 0 |
Store 3 | 0 | |
Store 4 | 0 | |
Store 5 | 0 | |
Store 6 | ||
Store 7 | 0 | |
Store 8 | 0 |
I would like to make new visual table with the same dimensions and same output of Store, but where there are "0" in visual table above, I would like to display average sales (think sales lost. This average is calculated from all Store with sales, and I have a measure for this. But it is not working in this context, because when I involve Store in table, the average is calculated per Store.
1) How do I alter measure below to disregard the Store dimension of my data? There are other dimensions that I would like to keep, but not Store.
Product Category | ||
Store | Alfa | Bravo |
Store 1 | 12 | |
Store 2 | 9 | |
Store 3 | 9 | |
Store 4 | ||
Store 5 | 9 | |
Store 6 | ||
Store 7 | 12 | |
Store 8 | 12 |
/depple
First, for the average part:
use the CALCULATE function and ALL or ALLSELECTED as follows:
AvgSales = DIVIDE(CALCULATE([Sales],ALLSELECTED(Data));'Data[Distribution]))
Now for placing the average instead of zeros, you can use a measure in the table instead of the Sales column:
_Sales = if(SELECTEDVALUE(Data[Sales]) = 0, [AvgSales], SELECTEDVALUE(Data[Sales]))
It would be better if you share the pbix file so that I make sure the calculations are correct
Hi @yelsherif ,
Thank you for your suggestion.
I was just about to delete my post, as I managed - by extensive trial & failure - to come up with a working solution. Unfortunately I immediately struck a new problem, because my matrix's vertical sums is returning values that appear to be - if not - close to averages. I have never experienced that before, so I guess that I could have something to do with the extremely homemade measures. I will try your measures, surely they are better, and maybe that'll do the trick!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |