Percentage of total aggregated by category :(

[powpowpow]

Hi Everyone, 

I am new to Power BI and have not been able to figure out this problem for the last 2 weeks...

In order to see if an item is available at a select point of sale, I created a column that has a value of 1 if an item is in stock and 0 otherwise. (Simple measure "Availability" =  if (stock units >1, 1, 0) )

Now, I am interested in seeing the presence rate percentage for each item by retailer. This means that I want the numerator to be equal to the sum of "Availability" accross all point of sales (so that it totals the number of point of sales where my item is in stock, per retailer). I want the denominator to be equal to the total number of stores considered. 

Sample data (for one item and two retailers but there are so many)

ITEM ID	RETAILER	DOOR#	STOCK	Availability
0151	BCB	BCB#001	1,200	1
0151	BCB	BCB#002	0	0
0151	BCB	BCB#003	15	1
0151	BCB	BCB#004	0	0
0151	BCB	BCB#005	536	1
0151	BCB	BCB#006	987	1
0151	BCB	BCB#007	0	0
0151	BCB	BCB#008	0	0
0151	BCB	BCB#009	134	1
0151	BCB	BCB#010	1,289	1
0151	APE	APE#001	476	1
0151	APE	APE#002	356	1
0151	APE	APE#003	0	0
0151	APE	APE#004	135	1
0151	APE	APE#005	12	1
0151	APE	APE#006	0	0

In this case, BCB has a total of 10 doors and APE has a total of 6 doors

My Dream Result is to create an availability percentage equal to (1+0+1+0+1+1+0+0+1+1)/10 = 60% for BCB and (1+1+0+1+1+0)/6 = 66.67% for APE. 

The problem:

Item ID, Door #, Stock Units are all in different tables. 

Retailer and Door# are in the same table

I CANT FIGURE OUT THE DAX FORMULAS TO CALCULATE THE AVAILABILITY PERCENTAGE. 

It needs to work for all items and for any retailer i select in my slicers 

Items are grouped by size (big  - medium - small) so ideally, I can filter on the size category and still get the availability percentage. (size is in the same table as item)

I know that its a lot of info but need help from the pbi community. SAVE ME PLZ

[az38]

Hi @powpowpow 

your mention about different tables and slicer makes this task harder without data model, but as simple option you could use a measure

Dream result measure = 
DIVIDE(
CALCULATE(SUM('Table'[Availability]), ALLEXCEPT('Table', 'Table'[ITEM ID], 'Table'[RETAILER]) ),
CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[ITEM ID], 'Table'[RETAILER]) )
)

[powpowpow]

Hello, 

Thanks so much for considering this post. 

Unfortunately Availability is a measure so I cannot sum it this way in DAX

The table I provided is fictional so I cant apply 'all except' on it. Provided the closest approximation to the data model I could in the above reply to Martyn. 

Please let me know if you need anything else

Thanks!!!!!!!!!!!!

[MartynRamsden]

Hi @powpowpow 

This sounds doable but in order to help you, we need to understand your data model a little better.

Are you able to share you pbix? If not, could you post some sample data but displayed in separate tables just like it exists in your model?

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[powpowpow]

Hi Martyn, 

Thanks a lot for your response, really appreciate it.

I am unfortunately not able to share my pbix for confidentiality reasons but here is how the data model looks like.

ITEM Table

ITEM ID	STOCK	PRICE
0151	100,000	12.5
0121	225,000	22.3
0133	8,000	19.55

ITEM Detail Table

ITEM ID	Category	PRICE
0151	Big	12.5
0121	Small	22.3
0133	Small	19.55

Retailer Table:

RETAILER	DOOR#	DOOR#
BCB	BCB#001	BCB#001
BCB	BCB#002	BCB#002
APE	APE#003	APE#003

The tables are all related one way or another and the data warehouse is a cube connected live to the BI

PS. Availability is a measure i created that is 1 if there is stock and 0 otherwise (so i cant sum it in DAX)

Hope this helps, 

Thanks so so much