Forum Discussion

paa_paa's avatar
paa_paa
New Member
8 years ago

Measure over Pivot Table

Hi there,

 

I have a dataset containing the list of new clients. The simplified version of that data set would have the following columns:

  • Name of client
  • Area where client lives
  • Subarea where client lives
  • Client is type A (1/0)
  • Client is type B (1/0)
    • Type A + Type B = 1
  • Date the person became new client

 

I am currently trying to do what follows:

  • I have a measure that tells me the number of clients that I can filter (using slicers) by area, subarea and date
  • I have a measure telling me the ratio of clients A vs B (sum clients A / sum clients B) that updates as I filter by area, subarea or date

 

So far so good. The thing is that I want to be able to add a measure stating the max ratio A vs B in a Subarea for the given date range, and here is where I am having some trouble, as I cannot manage to come up with this measure. If I create a Table visualization, I can get the information I need, as I get a table with

 

Subarea1 - Number Clients A - Number Clients B

Subarea2 - Number Clients A - Number Clients B

...

 

I would just need to do max of that table, but I don't know how to 'create' that table inside the measure. Any help?

 

 

Thanks!!

 

This table gets updated as I select a date on the slicer

 

 

I can easily create a Table visualization that updates this

 

1 Reply

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    paa_paa

     

    Without knowing some sample data, it is hard to understand your requirement clearly.

     

    Thereby, could you please share us some sample data (which we can copy and paste directly) and its corresponding desired result? Also, as you said you want to get the max ratio A vs B and you have specified the ration measure. Could you please share us this measure expression? So that we can get a right direction.

     

    Thanks,
    Xi Jin.