Query Issue

[sajarac]

Hi there, I am having this issue, and I can not find the way to produce the report needed.

In my data table I have several columns but the most important for me now are:

AuditFY

Customer

Sales Rep

JobFY

When I get the job I recorded the "AuditFY" but the customer could accept the job in the same FY or in a Different FY "JobFY"

For both "AuditFY" and "JobFY" I have dropdowns menus like "FY2019", "FY2018", FY2017"...etc

If I try to run a query without any filter just the query to see AuditFT, Customer, Sales Rep, JobFY, I can see all of the data. but once I try to see any specifit FY it doesn't match for both.

Somethin like this:

Sorted by AuditFY

AuditFT AuditValue JobFY Job Value Sales Rep ID

FY2019	$6,860.00	FY2019	$6,860.00	219
FY2019	$351,641.00			252
FY2019	$20,670.00			368
FY2019	$65,260.00			360
FY2019	$23,140.00			208
FY2019	$95,211.00			276
FY2019	$145,191.00			126
FY2019	$17,711.00			252
FY2019	$42,861.00			368
FY2019	$29,401.00			309
FY2019	$56,161.30			365

Sorted by JobFY

AuditFY AuditValue JobFY Job Value Sales Rep ID

FY2019	$6,860.00	FY2019	$6,860.00	219
FY2018	$40,000.00	FY2019	$8,586.00	317
FY2018	$23,200.00	FY2019	$13,572.00	239
FY2018	$52,790.40	FY2019	$2,613.00	368
FY2018	$41,457.00	FY2019	$41,457.00	368
FY2018	$39,607.00	FY2019	$39,607.00	217
FY2018	$4,699.50	FY2019	$4,699.50	343
FY2018		FY2019	$42,205.00	368
FY2018	$28,865.20	FY2019	$28,865.20	368
FY2018	$31,289.00	FY2019	$31,288.40	368

So in summary:

I would like to see my query sorted like:

IF(AuditFY=FY2019, and JobFY=2019) Sum [AuditValue]+[Job Value]

Thanks in advance for any help.

Regards,

[v-cherch-msft]

Hi @sajarac

It seems you may check the relationships to solve this issue. If you don't want to change the relationships,the other way is that you may add two slicer tables to use and make sure there is no relationship for the two tables. For example:

AuditValue2 =
CALCULATE (
    SUM ( Sample1[AuditValue] ),
    FILTER (
        'Sample1',
        Sample1[AuditFY] = SELECTEDVALUE ( SlicerAuditFY[AuditFY] )
    )
)

JobFY2 =
CALCULATE (
    SUM ( Sample1[JobValue] ),
    FILTER ( 'Sample1', Sample1[JobFY] = SELECTEDVALUE ( SlicerJobFY[JobFY] ) )
)

Sum2 =
SUMX (
 FILTER ( Sample1, [AuditValue2] <> BLANK () && [JobFY2] <> BLANK () ),
 [AuditValue2] + [JobFY2]
)

Regards,

Cherie

[sajarac]

Hello there, and thank you very much for your response.

Your interpretation is correct and that is exaclty what I need, however. I have been trying to replicate your tables and I am totally stuck at the very beginning.

Could you please point me step by step?

Sorry about that.

Regards,

[v-cherch-msft]

Hi @sajarac

First, you need to create two slicer tables with DISTINCT Function. Then you may use them as slicers. Last, you may create measures  to get the sum value. Here is the sample file for your reference. Please let me know if you have other questions.

Regards,

Cherie

[sajarac]

Thank you very much again for your help and the file.

I don't know what's going on with my data or file, but for some reason I can not replicate the tables exactly as you have in your sample. sorry I feel bad, I am confident with the rest of the choices or formulas but I don't know what is exactly my issue.

😞

[v-cherch-msft]

Hi @sajarac

It's better if you could share some data which could reproduce your scenario and your desired output.You can upload it to OneDrive or Dropbox and post the link here or send me via private message.

Regards,

Cherie

[v-cherch-msft]

Hi @sajarac

I think maybe i cannot fully understand your request. Below is the simplified sample for your reference. If it is not your case, could you explain more about your expected output?

Regards,

Cherie

[sajarac]

Hi there, thank you very much for your promp reply, I think that we are getting close, but for some reason the table shows different values, I can not combine into a single list for both Audits + Jobs, please see the table tables below and note how the data changes: