Forum Discussion
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.
I want this outcome: where there are 0's, blanks, AND positive integers.
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
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).
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.
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?
