Calculation of a value with 2 possible outcomes

[Kevv]

I've spent a lot of time attempting to resolve this.  I hope someone can help.

I assist in providing funding to areas in the UK.  These areas overlap each other.  Where they do so the location/postcode counts as both areas.  I have 1 table that contains whether the postcode was funded, and this is linked to another table that indicates which area(s) the postcode belongs to.  Please see the example table below.

From above, the total count of A would be 4 and total count of B would be 3.  However, what I would like to show in my chart is:

A-Funded=3

A-Not Funded=1

B-Funded=2

B-Not Funded=1

Currently I can only show the values realted to Area_01 using the linked table as below.

I hope this make sense.  Any help would be very much appreciated.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Kevv]

Thanks for looking at the problem Greg & Ashish.

Unfortunately, I'm not permitted to share the dataset or related files by law, so I'll have to create a pseudo dataset and pbix for you to look at.  This is partly the reason for the delay.

Thanks for your patients.

[Greg_Deckler]

@Kevv- Understood, that's fairly common. Remember, just a few lines of example data that simulates your real data is generally required. You typically do not need a lot of it.

[Kevv]

 

Good evening chaps

I've made some time over the weekend to create files I can share.  The image above (1st table) demonstrates how the raw data looks(ish) and the linked file how it looks with the unrolved issue.

Within the above image, the lower table (manually generated) is the source for the chart (also above).  The chart is the result I want to get to as opposed to the link.

Link:
https://app.powerbi.com/view?r=eyJrIjoiYTc2MDE0MDgtZmUxMi00ZmEwLWI1MjctNjAzZjMxODNlZTJiIiwidCI6ImI1Z...

As you can see the link isn't including the LEP_02 from the second column of the table.  I've attempted many solutions but have exhausted my skill level.

These are the relationships:

Tables 2015_LEP, 2016_LEP & 2017_LEP are used to create a simplified table LEP_NOMIS and the Unique_Year Table.

 

Relationships are:

Funding_Report [Postcode_ofBusiness] to Postcode_Conversion [Postcode]

Funding_Report [Date_of_Funding] to Unique_Year [Year]

Table LEP_NOMIS is for comparative values once the issue has been resolved (relationship of no value at this time).

I hope this explains the issue better.  If you are able to help, I would be very appreciative.

With regards

K

[Ashish_Mathur]

Hi,

You need to use the "Unpivot other columns" feature of the Query Editor to conver the first table into a 5 column one - the first 4 columns staying the way they are and the last 2 becoming a single column.  In the Query Editor, select the first 4 columns, right click and select "Unpivot other columns".

Hope this helps.

[Kevv]

Ashish

Thanks; I can see how that might work, however I've misrepresented the table by accident in the blue table.  LEP_01 & LEP_02 fields do not exist within it, they exist as a separate table.  I've just edited my post to reflect this.  Sorry for the inconvenience.

If you look at the table relationship image below you can see how they’re connected.  The reason for this it that the table Funding_Report is used to calculate another set of charts.  If I were to combine the Postcode_Conversion table and then unpivot, the charts would show duplicate values.

I'm reluctant to create 2 Funders_Report tables (1 standard & 1 unpivoted) due to the number of records in the original table, thus doubling the number records.

I did attempt to use your method on the table Postcode_Conversion, however the relationship no longer works as the postcode field no longer contains unique values.

Thanks for your speedy response though, and I hope you're able to help again.

With regards

K 

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Please describe the question, explain the dataset and show the expected result.  

[Kevv]

Ashish

Thanks for staying with me.  I needed to create a version & place I could share.

Here's the link:

https://drive.google.com/open?id=1L9D1Ll65_rCXNykYGC8HhXdIw-C5p7xB

Chart:  Region is fine.

Chart: LEP only picks up the values from LEP_1.  It needs to look like the chart below.

Thanks for looking at this Ashish.

K

[Ashish_Mathur]

Hi,

I am completely confused.  Please share data only that is relevant (remove the unnecessay tables/visuals).  Also, share the Excel file so that i can edit your queries, if i need to.  Please also describe your business requirement.

[Kevv]

Ashish

I've re-worked a few files to explain what I'm attempting to achieve.

The chart for LEP should look like this image below.

Link to video file explaining how the calculations are done manually.

https://drive.google.com/open?id=1L9D1Ll65_rCXNykYGC8HhXdIw-C5p7xB

Link to pbix.

https://drive.google.com/open?id=1bM26wramsDaV6puKoCcE0UF-5HFwuR6g

Link to workbook data source.

https://drive.google.com/open?id=127kDN6nTglHYfA-0Gohl3P1GvyD8fxE1

I hope this makes things clearer.

K

[Ashish_Mathur · Accepted solution]

Hi,

I believe this is the result you want.  You may download the file from here.

[Kevv]

Ashish

I’ve tested out your solution with the real report and it works well.  Truly amazing; there's no way I would have figured out that solution.

Thanks very much for persevering with me.  Guess I'll have to practice a lot more.

Thanks again;  your help is very much appreciated.

Kind regards

Kevv

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

I do not get the sense that the information you provided represents your raw data. If you can provide sample/example raw data in a format that can be easily copied and pasted that would be very helpful.