Forum Discussion

rhl94's avatar
rhl94
Icon for Advocate III rankAdvocate III
7 years ago
Solved

Recreating the following excel table(s)

Hi

 

I am looking to create a similiar table to what we currently has in excel:

So far we have the data available in Power BI, but structured differently. This is how the table is structured in Power BI:

 

 

It is easy to re-create the table on the left as a matrix visual, however I need to perform calculations based on the table, so that wont work. I've tried to create a calculated table that is summarized as such;

 

Table =
SUMMARIZECOLUMNS (
    [Town];
    "Network1"; CALCULATE (
        DISTINCTCOUNT ( [Number]);
        [Network] = "Network1"
    );
    "Network2"; CALCULATE (
        DISTINCTCOUNT ( [number] );
        [Network] = "Network1"
    );
    "Number"; DISTINCTCOUNT ( [Number] )
)

 

, however I need to filter date(which I didnt include in the mockup) and a few other things, and that doesn't work with the filter.

Need Help
  • Icey's avatar
    Icey
    7 years ago

    Hi rhl94 ,

    You can try this:

    1. In Power Query Editor, Pivot column 'Network'.

    2. Create measures.

    Total Number = SUM ( 'Table'[Network1] ) + SUM ( 'Table'[Network2] )
    Network1 % = DIVIDE ( SUM ( 'Table'[Network1] ), [Total Number] )
    Network2 % = DIVIDE ( SUM ( 'Table'[Network2] ), [Total Number] )
    Total Percent = [Network1 %] + [Network2 %]

    Best Regards,

    Icey

     

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

     

2 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi rhl94 ,

    You can try this:

    1. In Power Query Editor, Pivot column 'Network'.

    2. Create measures.

    Total Number = SUM ( 'Table'[Network1] ) + SUM ( 'Table'[Network2] )
    Network1 % = DIVIDE ( SUM ( 'Table'[Network1] ), [Total Number] )
    Network2 % = DIVIDE ( SUM ( 'Table'[Network2] ), [Total Number] )
    Total Percent = [Network1 %] + [Network2 %]

    Best Regards,

    Icey

     

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

     

    • rhl94's avatar
      rhl94
      Icon for Advocate III rankAdvocate III
      7 years ago

      Hi Icey and thank you for your answer

       

      The problem is - and I should probably have included that in the last table in my post - I have a date column and a category column (what category is the number from) that I would like to filter upon in my report. When I pivot the table like you suggested, I lose the possibility of filtering them by date and category. 

       

      For example, I would only like to a report of numbers from 2019 and of the type "fruit" for example.