Can't get report to use unique values and not sums from dataset

[jhwrl]

Hi, 

I hope i can make this issue clear, as i am sure its quite simple to fix if you know how, but i am stuggling to get the right result. 

My dataset has rows of 'job applications' which are associated to a specific job and their views/applications (represented in differnet columns). 

one column has total views and another is total applications for each job. 

These columns have the same number (total views+applications) in each row, depending on the specific job. So if 12 applications were made, you will see 12 rows (of different candidates) and the total views and applications will be the same in each of these rows. 

The issue comes when i get to reporting this data for specific jobs in charts

The total views and applications per job are represented correctly in table for, with Powerbi able to identify each unique job , however in graph form or totals/card views as well as applications are not represnting correctly. 

For total views they show the sum of all the views by row, so if 12 applications were made it is giving the vaue of the total views x12, and not a a unique value per job - ie just the total views. 

Does anyone know what process I need to do to achieve the results required and get the total views and applications to take unique values per job so that chart data can be true.

By the way, just changing to disticnt count will not work. 

thanks 

[amitchandak]

@jhwrl ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You have to try a formula like this based on what I got now

sumx(values(Table[job application ID]), calculate(Table[view]))

[jhwrl]

Hi @amitchandak , thanks for your reply.

below are a few screenshots. 

you see from the first one, the vacancy totals are not showing for cards, based on the specific job highlighted. Perhaps i can not expect this to happen? 

the biggest issue is as per the second screenshot, is that the when selecting specific employers via a slicer or similar, that the overall application number is correct, but the views are not, these are adding all the applications together. 

the third image shows the dataset and how its constructed for one specific job (ie job1)- each row represents an application.

[v-shex-msft]

Hi @jhwrl,

Table visuals can display the records and split to detail records based on the group/category(the fields you setting to 'do not summarize') fields.
When you use the field on card visual, they will be forced aggregated to displayed with the summarized value. (text or other types of values which not able to be aggregated will be summary with count/first/last mode, numeric values will be aggregated with sum/average/min/max... mode)

For your scenario, I'd like to suggest you use multi-cards visual which allows using value fields and category fields at the same time.

Work with aggregates (sum, average, and so on) in the Power BI service 

Regards,

Xiaoxin Sheng