Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Hi @edatero
Have you tried creating a column with the formula ="IF(IsBlank(PROD1) & NOT(ISBLANK(ASSOC1), 0, PROD1)"?
shebr
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?
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
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
Can you post some example source data that you are trying to transofrm in the end result
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.
What are you using to calculate ASSOC1?
Can you please post it so that I can work of of that
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).
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |