Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago
Solved

Variance report using Metrics visual and static table

I am trying to create a variance report using a Metrics visual and a static table (that I am thinking about having a static data in SQL).   I created a metric visual as shown below: These nu...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi JustinDoh1 

    I think you want to build a static table in data model like second screenshot.

    You can try to use Power Query to transform your data model. I build a sample like yours to have a test.

    Sample:

    Matrix:

    Firstly we need to use Group By function to get Count column as below.

    Then we pivote "Matrix Column" as below to get the result.

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

     

     

  • JustinDoh1's avatar
    JustinDoh1
    5 years ago

    Anonymous 

    Thank you so much for your feedback.

    I ended up doing this process:

    1) Created a static table (Table2) for the second data.

    2) Created a joined key (called 'JoinKey') for the two tables (Table1 and Table2).

    3) I joined Many to Many relationship for the two tables (using the joined key 'JoinKey').

    4) Tried with Quick Measure (for the first time 🙂 ).

    It generated a measure like this:

    Count of JoinKey minus Count of JoinKey = COUNTA('table1'[JoinKey]) -
    COUNTA('table2'[JoinKey])

     

    It worked somehow.  🙂

     

    I have a question. What is risk of having Many to Many relationship?

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi JustinDoh1 

I think you want to build a static table in data model like second screenshot.

You can try to use Power Query to transform your data model. I build a sample like yours to have a test.

Sample:

Matrix:

Firstly we need to use Group By function to get Count column as below.

Then we pivote "Matrix Column" as below to get the result.

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

 

  • JustinDoh1's avatar
    JustinDoh1
    Post Prodigy
    5 years ago

    Anonymous 

    Thank you so much for your feedback.

    I ended up doing this process:

    1) Created a static table (Table2) for the second data.

    2) Created a joined key (called 'JoinKey') for the two tables (Table1 and Table2).

    3) I joined Many to Many relationship for the two tables (using the joined key 'JoinKey').

    4) Tried with Quick Measure (for the first time 🙂 ).

    It generated a measure like this:

    Count of JoinKey minus Count of JoinKey = COUNTA('table1'[JoinKey]) -
    COUNTA('table2'[JoinKey])

     

    It worked somehow.  🙂

     

    I have a question. What is risk of having Many to Many relationship?