cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edatero Frequent Visitor
Frequent Visitor

matrix

Hello,

 

I am creating a Matrix / Pivot Table on Power BI. My 2 values per column id are both Distinct Counts.

 

Is there a way I can combine the columns, so that IF the PROD1 column is empty and ASSOC1 column is not, it returns a 0, or else it shows the PROD1 number?

 

Example: Product #11 would show a 0 where ASSOC1 is 1.

 

bi1.JPG

 

I want this outcome: where there are 0's, blanks, AND positive integers.

power2.JPG

 

I have excel experience, but can't seem to figure out syntax here. Also, is there a way to only have one column, with the calculation stated above? 3 Columns seem cluttered.

 

Thank you for your time!

8 REPLIES 8
ThomasFoster Established Member
Established Member

Re: matrix

Can you post some example source data that you are trying to transofrm in the end result

edatero Frequent Visitor
Frequent Visitor

Re: matrix

Thank you for replying! Sure thing,

 

The first table is where id represents Products, and each Manufacturer of the specific product has its own numeric id.

 

The PROD1 Value in my matrix is the Distinct Count of how many Products each Manufacturer produces. (We can see that Manufacturer 1093 produces several items on this chart).

 

m_Product-Manu.JPG

 

This second table shows the relationship between Manufacturers and their respective Dealers/sellers.

 

In my matrix, the columns are Dealers, the rows are Manufacturers, and the Values are Count of Products (PROD1) and if a relationship exists between Dealers and Manufacturers (ASSOC1). I want a 0 to show if this relationship exists, but the Dealer has not received any of the manufacturer's items.

 

m_Manu-Dealer.JPG

 

Highlighted
ThomasFoster Established Member
Established Member

Re: matrix

What are you using to calculate ASSOC1?

 

Can you please post it so that I can work of of that

edatero Frequent Visitor
Frequent Visitor

Re: matrix

Yes! In the second table I posted, Dealers and Manufacturers are associated to each other by their arbitrary I.D. "key." If a key exists, it simply shows as "1" in the ASSOC1 column (The calculation is Distinct Count of these keys for each respective column-row).

shebr Member
Member

Re: matrix

Hi @edatero

 

Have you tried creating a column with the formula ="IF(IsBlank(PROD1) & NOT(ISBLANK(ASSOC1), 0, PROD1)"?

 

shebr

edatero Frequent Visitor
Frequent Visitor

Re: matrix

Hello, Thank you for the suggestion!

 

Unfortunately, Yes, I have tried that and several variations. I have also tried attaching Count() and DistinctCount() for the measures.

 

Is it possible to create a formula that accounts for matrix Values, not just the columns and rows?

Perhaps a FOR loop for each Value that appears in the matrix?

shebr Member
Member

Re: matrix

Hi @edatero

 

I dont believe so no, but im also struggling to understand exactly what it is you are trying to achieve. It sounds like its possible but its not the solution you want. Can you perhaps go through this again step by step? Or perhaps provide the pbix file or analysis?

 

Thanks

 

shebr

Community Support Team
Community Support Team

Re: matrix

Hi @edatero,

 

Maybe you could ceate measures to get the distinct count of PROD1 and ASSOC1. Then, create a third measure to combine their values like:

Measure=IF([MeasurePROD1]=BLANK() & [MeasureASSOC1]<>BLANK(), 0, [MeasurePROD1])

 

For more advice, please show us your desired output. Based on current description, I was confused about what you were trying to achieve.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.