Forum Discussion
Helper IVSpecial Calculated Column Requirement in Matrix (PBIX Attached)
Dear PBI Experts,
I am struggling with a recent requirement and not sure whether/how it can be addressed in Power BI. I tried developing my PBI as far as I could, but there is one requirement where I am totally stuck (RED Highlighted Columns in my matrix/2nd Pic Screenshot).
My Power BI : https://www.dropbox.com/s/l10fcravu6exloe/PBI_TEST.pbix?dl=0
I have following 3 tables. [dim_Product] giving product level information. [map_ProductOwnership] gives information about which group and department a Product is mapped to and finally, [fct_Transaction] giving day to day transactions of Product. Not every day every product would be bought or sold.
Requirement is to create a Matrix View like below....
a) Show All Product and group and department for each transaction day, irrespective of whether there is any Buy/Sale. -- This one is simple - just enable "Show Items with No Data"
b) Show Buy/Sale and Total for each Product /Department / Group / Date along with Cash/Credit mode. If Sum is nothing, feel free to have blank or 0. -- This one also addressed in my Power BI attachment.
c) Also Consolidate and show How many Products are there in each level (department/ group/date etc.) ; How many of them are Active / how many are transacted. i.e. either a Buy or sale event occured.
~ This is where I am stuck. Ideally the final look should be somewhat like below --- But I could not address the construction of these 3 red highlighted columns.
My Power BI (attached) As of now looks like below. Could some expert please help to achieve the goal here?
2 Replies
- amitchandak
Super User
RKM , For active inactive follow renetion logic. Sum up Buy and sell mesure without +0
and then use duration meausre like MTD/LMTD or QTD/LQTD etc to check for blank and non blank
example
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458 
RKMNot sure if I understood that correctly, or if those are similar scenarios....
As the PBI file attached - I am struggling to add these 3 red columns. I can try calculating them with some custom measure, but then Already in my matrix there are two columns... Mode (for Cash or Credit) & Direction (Buy Or Sell). The moment I am trying to add any new column or value its getting totally messed up and repeating all columns everywhere. But again... adding more calculation on Raw will also not help because mostly all fields will be collapsed (expanded when necessary), so any new Raw attribute will be hidden,
