DAX Measures

[wesley.yau]

I have two sets of data set up as shown below.

Records of items sold - keeps track of the number of coloured shapes sold. [TABLE="width: 500"]
<TBODY>[TR]
[TD]Shape
[/TD]
[TD]Colour
[/TD]
[TD]Volumn
[/TD]
[/TR]
[TR]
[TD]Sphere
[/TD]
[TD]Red
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]Sphere
[/TD]
[TD]Blue
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Sphere
[/TD]
[TD]White
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Sphere
[/TD]
[TD]Red
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Rectangle
[/TD]
[TD]Blue
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]Rectangle
[/TD]
[TD]White
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]Rectangle
[/TD]
[TD]Red
[/TD]
[TD]35
[/TD]
[/TR]
[TR]
[TD]Rectangle
[/TD]
[TD]Blue
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Rectangle
[/TD]
[TD]White
[/TD]
[TD]20
[/TD]
[/TR]
</TBODY>[/TABLE]


Total number of items in stock
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Shape
[/TD]
[TD]Colour
[/TD]
[TD]Stock
[/TD]
[/TR]
[TR]
[TD]Sphere
[/TD]
[TD]Red
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]Sphere
[/TD]
[TD]Blue
[/TD]
[TD]130
[/TD]
[/TR]
[TR]
[TD]Sphere
[/TD]
[TD]White
[/TD]
[TD]120
[/TD]
[/TR]
[TR]
[TD]Rectangle
[/TD]
[TD]Red
[/TD]
[TD]130
[/TD]
[/TR]
[TR]
[TD]Rectangle
[/TD]
[TD]Blue
[/TD]
[TD]140
[/TD]
[/TR]
[TR]
[TD]Rectangle
[/TD]
[TD]White
[/TD]
[TD]150
[/TD]
[/TR]
</TBODY>[/TABLE]
But total sphere cannot be sold more than 300 and total rectangle cannot be sold more than 350

How do I go about setting up a custom "Percentage Sold" measure like the following?

[Shape] [Colour] [Percentage Sold]
Sphere Red 50% (50 / 100)
Sphere Blue 7.69% (10 / 130)
Sphere White 4.17% (5 / 120)
Total Sphere 21.67% (Instead of 65 / 350, I need 65 / 300)

Same goes for rectangle.

Any help would be appreciated, thanks!

 

[MickG]

I've just realised this was in "PowrPivot", but the code may be of some Help !!!
With you Stock items in Columns "A,B & C" and you Sold Items in Columns "F,G & H", Starting row1 with the headers.
This code will provide results in column "D" starting "D2".

[COLOR=navy]Sub[/COLOR] MG19Nov33
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] shp         [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("F2"), Range("F" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    shp = Dn & Dn.Offset(, 1)
    [COLOR=navy]If[/COLOR] Not .exists(shp) [COLOR=navy]Then[/COLOR]
        .Add shp, Dn.Offset(, 2)
    [COLOR=navy]Else[/COLOR]
         .Item(shp) = .Item(shp) + Dn.Offset(, 2)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    shp = Dn & Dn.Offset(, 1)
        [COLOR=navy]If[/COLOR] .exists(shp) [COLOR=navy]Then[/COLOR]
            Dn.Offset(, 3) = Format(.Item(shp) / Dn.Offset(, 2), "0.00%") & " (" & .Item(shp) & "/" & Dn.Offset(, 2) & ")"
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[wesley.yau]

Thanks for the reply Mick, your approach does the job with code but I'm actually looking for a solution with powerpivot (expecially writting the DAX part to display the custom "total sphere")

But, nevertheless, I appreciate your effort and help!

Anyone else got clues on how to achieve this?

 

[powerpivotpro]

I'm working on this as today's blog post. Should be live in less than 30 minutes

 

[powerpivotpro]

Post is here:

Budget Calculations That Change by Level « PowerPivotPro

Let me know if I answered the wrong question

 

[wesley.yau]

Exactly what I'm looking for, thanks a lot for the great post!

Wes