PowerPivot - how to count at lower level even when aggregating? RRS feed

  • Question

  • Hi,

    I am sure that this issue has been discussed before, but I am new to PowerPivot and I am not even sure how to formulate my question for a search. Basically..

    I have a Client Hierarchy: Client Group==>Client Company==>Client Entity — where Client Entity is the most granular. I am calculating Profit Margin at Client Entity level and want to group into categories: Low if margin is less than 30%, medium if between 30&60 and high if above 60%. I then want to show in a visual representation, how many entities are within these 3 categories.  I need this flexible, as I want to use a slicer for a location, etc.

    I have a measure formula, but it groups up if I remove entity dimension. I basically want this to be always calculated at entity dimension.

    ProfitMargin is a calculated field from another measure formula.

    =if([ProfitMargin% Adjs]<=0.3,”Low”,if([ProfitMargin% Adjs]<0.6,”Medium”,”High”))

    Thank you,


    Monday, October 29, 2018 3:02 PM

All replies

  • Hey Dovile. Generally I would do this type of thing as a calculated column. Reason being that you can then put the column on an axis to group the client entity. Do you have a measure for profit margin? If so, just add it to a new calc column in the client table (I assume the client entity is the primary key of the dimension table). Eg. =[profit margin%] This will tell you the margin per client entity. You can then edit this column with an if statement or switch statement to create the bands. Something like this. = var margin = [profit margin %] Return SWITCH(true(),margin<0.3,”low”,margin<0.6,”med”,”high”) From there you can use the new column to group the clients in a visual.

    Self Service BI Expert using Power Pivot

    Tuesday, October 30, 2018 8:27 AM