Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RKM
Helper IV
Helper IV

Special 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.

 

RKM_0-1687106441509.png

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.

RKM_1-1687106757485.png

My Power BI (attached) As of now looks like below. Could some expert please help to achieve the goal here?

 

RKM_2-1687107389212.png

 

 

2 REPLIES 2
RKM
Helper IV
Helper IV

Not 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,

RKM_0-1687196998595.png

 

amitchandak
Super User
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/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.