Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Show only selected category's value

Hi all,

 

I have a data set like that;

InvoiceNoMaterialCategoryValue
0001AX20
0001BY30
0001CZ40
0001DX10
0001EX5
0001FZ25
0001GY30

 

I want to create 2 table chart,
first one shows invoiceNo, Material and value. Second one should be matrix table and show invoiceNo, Material, value and "value of selected category". (I will explain that)

 

If I click on row 'C' on the left table, it should be return only same category's value with material 'C' on the right table.
(Material C category is 'Z'. Materials with category Z are C and F)

 

 

One more example 


How can i create a measure to return this value ?
It is really important for me. Waiting for your helps.

Regards

 

Need Help
Show and Tell

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    You can follow the below steps to achieve it(please check the below screen shot for the details):

    1. Create a dimension table Material

    2. Create a measure as below

    Measure = 
var _sel=SELECTEDVALUE('Material'[Material])
var _scat=CALCULATE(MAX('Invoices'[Category]),FILTER(ALLSELECTED('Invoices'),'Invoices'[Material]=_sel))
return 
CALCULATE(SUM('Invoices'[Value]),FILTER('Invoices','Invoices'[Category]=_scat))

    Best Regards

    Rena

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous hi,

      First off all thank you for your solution. It is working.

      I'm working with direct query option. I wonder that is it possible to do that without creating new table. 
      I don't want to import data to PBI.

      Regards
      Gorkem

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago


      Hi Anonymous btw i tried the below steps, I couldn't get the expected result.
      What did I do wrong ? Could you please check my PBIX file ?

      Regards
      Gorkem

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous,

      don't care my last reply. I was in mistake.  PBI created relation between tables then mine was not working.

      It is working now but i want to ask one more question.

      I want to do that filtering without filter component. I want to make filtering by clicking on left table.
      Is it possible and how ? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        I'm sorry I haven't found any other suitable method except adding a material dimension table to filter the data. If you directly click on material field in the left table, then the right table can only display the corresponding selected material information due to the interactions of visuals and the data display on these two table visual are from the same one table...

        Best Regards

        Rena