Forum Discussion

Umadhandapani's avatar
Umadhandapani
Helper II
4 years ago
Solved

Row and Column Matching

    In the image, Org1 & Org1 has value, simillary Org2 and Org 4. Org3 and Org5 we don't have value, I want to remove this against column wise and also other column instead of blank need to ...
Need Help
  • Umadhandapani's avatar
    Umadhandapani
    4 years ago

    Greg_Deckler 

    Herewith explained the flow,

    Table 1 : 

     

    Table 2 :

     

    I have used the matrix table,

     

    But need to replace blank with zero. So, I used the following measure,

     

    Measure = IF(SUM('Table'[Column2]) = BLANK(),0,SUM('Table'[Column2]))

     

    But, those entire column and row need to be removed where value is zero.
    So, I followed the following measure,

    Measure 2 = IF([Measure] = 0,0,1)
     
    where I have used in filter pane.
     

     

    For  row wise it is perfect, but for column it is not working.

     

    Expected Output to be,


     

Umadhandapani's avatar
Umadhandapani
Helper II
4 years ago

Greg_Deckler 

Herewith explained the flow,

Table 1 : 

 

Table 2 :

 

I have used the matrix table,

 

But need to replace blank with zero. So, I used the following measure,

 

Measure = IF(SUM('Table'[Column2]) = BLANK(),0,SUM('Table'[Column2]))

 

But, those entire column and row need to be removed where value is zero.
So, I followed the following measure,

Measure 2 = IF([Measure] = 0,0,1)
 
where I have used in filter pane.
 

 

For  row wise it is perfect, but for column it is not working.

 

Expected Output to be,


 

v-zhangti's avatar
v-zhangti
Community Support
4 years ago

Hi, Umadhandapani 

 

You can try the following methods.

New Table:

Table = 
Var N1=SUMMARIZE('Table 1','Table 1'[Column1],'Table 1'[Column2])
return
FILTER(N1,[Column2]>0)

Measure 1 = IF(SUM('Table'[Column2])=BLANK(),0,SUM('Table'[Column2]))
Measure 2 = IF(SUM('Table'[Column2])<>0,1,0)

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.