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 replace with zero. 

I have tried to replace with zero using measures, but it showing as,

 

 

But, need in this form,

 

 

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,


     

3 Replies

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