0

I have 15 columns in a power bi table. I would like to create new measure that will return the column names if the percentage value of any columns is less than 60%.

Example table:

ID total col2 col3 col4 col5
a100 50 35 10 5 6
a101 36 25 5 12 18

I created a new measure for each column that shows the percentage

%col2 = SUM(col2)/SUM(total)*100
%col3 = SUM(col3)/SUM(total)*100
%col4 = SUM(col4)/SUM(total)*100
%col5 = SUM(col5)/SUM(total)*100

By the new measure above, I will get col2 >60%

What I would like is to create a visual, maybe by kpi or a table that will return only the columns that have less than 60%.

I tried the following:

col_to_improv = SWITCH(TRUE(), OR(table[col2] < 60, "columnname", table[col3] < 60, "col_name2", table[col4] < 60, "col_name3"), table[col5] < 60, "col_name4],"")

I would like to show only the column names that has less than 60%, otherwise, do not show.

From the above example table, In the kpi (or other visual), I am expecting only col3,col4, and col5, because they have less than 60%.

Darkitechtor
  • 355
  • 1
  • 2
  • 12
budding pro
  • 135
  • 1
  • 2
  • 10

2 Answers2

1

I would advice you to transpose your table (if it's able) because it's easier to work with values than column names.

enter image description here Don't forget to add to your table one row with metrics description (Column1 in my example)

If you do so you with get desired result with pretty easy DAX measure:

col_to_improv =
SWITCH(
    TRUE,
    SELECTEDVALUE('Table'[Column2]) < 60,
    SELECTEDVALUE('Table'[Column1])
)

enter image description here

Darkitechtor
  • 355
  • 1
  • 2
  • 12
  • Apologies, I should have made it clearer. In the table were rows of values, and the avg row as shown in the example is not really there but as a result of a 'new measure' – budding pro Jul 01 '22 at 00:24
1

Column names - [Table Name],[Column Name] comes from COLUMNSTATISTICS(), so, don't change them, the rest you can name according to your model

VAR myTableStat=
        Filter(
            COLUMNSTATISTICS()
            ,[Table Name]="MyTable"
        )
        
VAR withSUM=
       ADDCOLUMNS(
            myTableStat
            ,"colSum",
                      SWITCH(
                               [Column Name]
                               ,"col2",SUM(MyTable[col2])
                               ,"col3",SUM(MyTable[col3])
                               ,"col4",SUM(MyTable[col4])
                               ,"col5",SUM(MyTable[col5])
                       )

                         
        )
VAR totalSum= SUM(MyTable[total])
VAR  withAveLessThen60 =
        FILTER(
            withSUM
            ,AND(
                DIVIDE(
                        [colSum]
                        ,totalSum
                )<0.6
                ,NOT ISBLANK([colSum])
            )
        )
            
VAR result = 
    CONCATENATEX(
                withAveLessThen60
                ,IF(
                    NOT ISBLANK([colSum])
                    ,[Column Name]                  
                )
                ,"; "
                
    )
        
RETURN 
      result

enter image description here

Mik
  • 2,099
  • 1
  • 5
  • 19
  • HI, Thanks. However, I cannot seem to make this work for the average value that is a measure and not actual rows from the table. I thought I am able to find a way to include that but the COLUMNSTATISTICS(), [Table Name], and [Column NAme] are throwing errors. For the ColumnsTatistics(): `PArameter is not the correct type` both [Table Name] and [column name] throw `Cannot find [Table Name]` and `cannot find [Column Name]` – budding pro Jul 01 '22 at 00:23
  • I tested the measure before I post and i got a result. Can you post your final code? Regarding measure instead of row. I’ll correct it on Monday. But if you put a code of measure instead of measure It may work. – Mik Jul 01 '22 at 05:00
  • But you get an error, so there is other problem as well. – Mik Jul 01 '22 at 05:06
  • What do you receive with ~CountRows(COLUMNSTATISTICS()) ~ – Mik Jul 01 '22 at 05:55
  • I replaced your line `"col1",AVERAGE(yourTableName[col1])` with `"ID",SUM(tablename[col2])/SUM(tablename[total])*100`. IT did not throw an error but the columnstatistics() and the column Name that you provided – budding pro Jul 01 '22 at 15:06
  • Sorry, I didn't get the point. Can you, please, explan once more? More details about what's happend. – Mik Jul 01 '22 at 15:09
  • If you'll add the full code of the measure I'd be able to define if the problem is in syntax. It can happens that due to a contex the tablename[col2] has zero rows qty and this can case an error as well. In general, the best way to get the result is check values step by step with a DAX studio, or by checking indirect values like CountRows() – Mik Jul 02 '22 at 09:58
  • Thanks, I updated the question above and included the measure code – budding pro Jul 05 '22 at 16:40
  • Hi @buddingpro. I altered the measure. In a DAX studio it works – Mik Jul 06 '22 at 06:11
  • You can mark it as answer if it's ok. – Mik Jul 07 '22 at 06:39
  • Thanks, and apologies, I am still playing around with it, and I am also checking in DAX studio. I am still getting the same error I mentioned before. – budding pro Jul 08 '22 at 21:32
  • I was able to make the measure work, but `couldn't load the data for this visual` further it says: `tablename[measure]:COLUMNSTATISTICS() cannot be used with a filter context.` – budding pro Jul 08 '22 at 22:47
  • @buddingpro. thank you for your feedback. I added a screenshot from my PBI. It means that the measure itself is correct, at least for the sample you gave. The problem comes from something else. I can't define how exactly comes the error. You can attach a dummy pbix file with you visual and data and your measure that gives the error. At least post your measure, so I can try to find the problem out. – Mik Jul 10 '22 at 07:25