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,


     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Umadhandapani What does your actual data look like?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

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