Recreating the following excel table(s)

[rhl94]

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.

[Icey · Accepted solution]

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]

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.