Forum Discussion
Bar visualization not outputting correct aggregated value on slicer
Hello, I have a table that looks like this (the total row is auto generated):
| Account Name | ID | Amount | string | keyword | select row |
| abc | 1 | 100 | test123 | test | 1 |
| abc | 2 | 300 | abtest | test | 3 |
| xyz | 3 | 250 | les | test | 0 |
| xyz | 4 | 50 | lestest | test | 4 |
| def | 5 | 100 | rest | test | 0 |
| ghi | 6 | 300 | testn | test | 1 |
| Total | 21 | 1100 | test | 0 |
I have 2 measures- keyword and select row, that are used in conjunction with a slicer to check if the 'string' column contains the keyword (I have a seperate table listing 6 keywords). In the example above, the slicer is filtered for keyword 'test'. 'select row' checks if the value in 'keyword' is contained in 'string', and outputs the location of the keyword in 'string'. So if 'select row'=0 that means the keyword doesn't exist in the string.
If I create a table, and specify the visual filter 'select row is not 0' meaning I just want rows of data with 'test' in the string, I get the following output which is correct:
| Account Name | ID | Amount | string | keyword | select row |
| abc | 1 | 100 | test123 | test | 1 |
| abc | 2 | 300 | abtest | test | 3 |
| xyz | 4 | 50 | lestest | test | 4 |
| ghi | 6 | 300 | testn | test | 1 |
| Total | 21 | 750 | test | 0 |
I want to create a bar chart visualization on this data, that cumulatively visualizes the total 'Amount' on 'Account Name' . I expect the output to say 'abc- 400', 'xyz-50' and 'ghi-300'- so exactly what is shown in the table above.
However, it doesn't work. Using a clustered bar chart I specify the axis='Account Name', Value= 'Amount' and create the visual filter like I did for the table 'select row is not 0', however the bar chart stays blank. It doesn't output anything. I'm wondering if it has to do with the 'Total' value of select row equaling zero, I don't understand how the total is calculated or why it is relevant.
For reference, if I make the axis equal to 'ID', which is a unique identifier, it works in that it outputs the 'Amount' values by 'ID', however making the axis 'Account Name' doesn't seem to work at all. Any help would be appreciated.
hi Anonymous
First, you should know that
Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, This is beacuse that you do not put string in bar chart as a row conext for the measure.
So just use this measure in visual instead of use [Amount] directly and do not use visual level filter any more.
Result = var _table=SUMMARIZE('Table','Table'[Account Name],'Table'[string],"_value",CALCULATE(SUM('Table'[Amount])),"_flag",SEARCH([keyword],SELECTEDVALUE('Table'[string]),1,0)) return SUMX(FILTER(_table,[_flag]>0),[_value])Result:
and here is sample pbix file, please try it.
Regards,
Lin
1 Reply
v-lili6-msftCommunity Support
hi Anonymous
First, you should know that
Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, This is beacuse that you do not put string in bar chart as a row conext for the measure.
So just use this measure in visual instead of use [Amount] directly and do not use visual level filter any more.
Result = var _table=SUMMARIZE('Table','Table'[Account Name],'Table'[string],"_value",CALCULATE(SUM('Table'[Amount])),"_flag",SEARCH([keyword],SELECTEDVALUE('Table'[string]),1,0)) return SUMX(FILTER(_table,[_flag]>0),[_value])Result:
and here is sample pbix file, please try it.
Regards,
Lin