Sum of distinct values and data not showing

[Anonymous]

Hi,

Hope you're fine. I hope you can help me.

I have an excel file (attached) with all projects awarded during 01/11/2019 and 31/01/2020. I want to know how many distinct ProjectID I have, and the the sum of their Pricetofunder.

For counting the number of projects, I'm fine ("Projects Awarded), but I struggled with calculating the sum. 

I tried this formula and did work for one period, but not for the one I'm attaching:

Funding Awarded =

SUMX(DISTINCT(Projects[ProjectID]),CALCULATE(MAXX(Projects,Projects[PricetoFunder]),USERELATIONSHIP('Calendar'[Date],Projects[BidAwardedDate])))

To understand why this measure didn't work for the attached period, I created a table to show the results. The table below shows from the attached excel file the projects (ProjectID), the PrincetoFunder and the BidAwardedDate. From PBI I added the "Count of ProjectID", Projects Awarded (I measure I added just to give more context), the total funding (SUM(Projects[PricetoFunder])) and the Funding Awarded (the measure I mentioned before that worked for other period).

The result is that 1) Count doesn't count some projects; 2) the Total funding is not created to the same projects not counted; 3) the Funding Awarded measure is not working for those not counted or summed. So, I thought that "Funding Awarded" didn't work because there was something wrong with those projects in the excel file (in fact, I attached as an excel to share with you, but it came from a json file). So I went to PQ and double checked their data type (decimal number for pricetofunder, and whole number for ProjectID). Also, I set their properties to "Don't summarize". Nothing changes as you can see.

Link to the file https://drive.google.com/file/d/1n_H_qVBCW16qjPKCkd5s2bMuz8yBSbR6/view?usp=sharing. I have highlithed those ProjectID duplicated and in Red the distinct. So, by filtering by the red I found that the "Funding Awarded" should be £1,489,566.38.

So, why ProjectID, Total Funding and Funding Awarded are not showing? I hope also the Funding Awarded gives the £1,4m from the excel.

Thanks for the help!

[amitchandak · Accepted solution]

@Anonymous , Try like

SUMX(summarize(Projects, project[ProjectID],"_1",CALCULATE(MAX(Projects[PricetoFunder]),USERELATIONSHIP('Calendar'[Date],Projects[BidAwardedDate]))),[_1])

[Anonymous]

Thanks. Returns blank values for all projects. 

I think my measure "Funding Awarded" worked, because as you can see it gives unique PricetoFunder for projects (e.g. 1201637 has 4 rows with same pricetofunder, but Funding Awarded shows only one PricetoFunder). 

So, i don't whether it's because Funding Awarded is not right (as you suggested), but I'm very cross of why not understanding why "Total funding", "Count of ProjectID"  are not showing in the table.

[parry2k]

@Anonymous is this what you are looking for?

[Anonymous]

@amitchandak @parry2k thanks for your answers. Yes, I see @amitchandak  measure worked and @parry2k solution provide same results. But I think they worked because you build your answers on my excel file (that I exported from my data). Same formula doesn't work with my dataset. As mentioned, somehow something is wrong with my data as some projects are not counted/summed. 

Can't see anything on the internet... How can I share my pbix file with you? As this has info from my organisation, I would like to limit this as to the period I refer to...and the data comes from json through an organisational API... so I don't know how or should share this with you...

[amitchandak]

@Anonymous , Check relation with Date Table. See if you can upload the file at dropbox or one drive

[Anonymous]

@amitchandak @parry2k thanks for you help, but I would rather not to share the file for security reasons.

@amitchandak , I did go to a previous version of my file and info not appearing got fixed, the measure worked if I created a new pbix file with the excel file I gave you using the following (withouth the userelantionship formula)

SUMX(summarize(Table1, Table1[ProjectID],"_1",(MAX(Table1[PricetoFunder]))),[_1]), but If I applied the formula you gave me 

SUMX(summarize(Projects,Projects[ProjectID],"_1",CALCULATE(MAX(Projects[PricetoFunder]),USERELATIONSHIP('Calendar'[Date],Projects[BidAwardedDate]))),[_1]) in my current pbix file, it returns blank spaces. The only difference is the USERELATIONSHIP, but can't think why returns blank spaces.

@parry2k , yes £1,489,566.38 is the value. Could you share the formula with me please? I did try several but nothing...

Thanks for both the help!

[parry2k]

@Anonymous as mentioned previously, share pbix file thru google drive/one drive, without looking into your file, it is very hard to tell why it is not working.

[Anonymous]

Hi all,

Thanks for your contribution. Finally @amitchandak  formula helped me.

Thanks again for you help.

[parry2k]

You can share using one drive/ google drive or send me directly via email. My email is in the signature.

[amitchandak]

@Anonymous , I create a new file on the data you shared. Check if that can help. The file is attached after Signature