Over-summing cumulative values in card visual?

[mrothschild]

I'm very new to BI and have only vague familiarity with pivottables, so my question is likely to seem very naive.

I have a data table, which I think is pivot table and BI friendly, i.e., each row is unique but may have duplicative data from a different row.  So the data is Project A, B, C, and then Setup Costs by A, B, C  and Sales for each Quarter for each Project.  

I'm trying to create a Card Visual in BI that shows Setup Costs, but since each row of information includes Setup Costs, it's over-summing the unique values.  

So I want the Card Visual output to be 275 [=50 + 100 + 125] but instead is outputting 1100, which is the full sum of the Setup Costs column

Project	Setup Costs	Quarter	Sales
A	50	1	5
A	50	2	10
A	50	3	15
A	50	4	20
B	100	1	5
B	100	2	10
B	100	3	15
B	100	4	20
C	125	1	10
C	125	2	20
C	125	3	30
C	125	4	40

[Anonymous · Accepted solution]

I think I have what you're looking for. To be clear, the 1100 is expected from PowerBI. If I were you, I would consider another data structure (e.g. separate tables) for this if possible, but if you can't, you can work around it. You could always do something simple like:

Setup Costs Total = CALCULATE(SUM(Sheet1[Setup Costs]),Sheet1[Quarter] = 1)

I wouldn't recommend this approach as it's hard-coded, and isn't very robust. I would suggest something more dynamic, such as:

Setup Costs Total 2 = SUMX(GROUPBY(Sheet1,Sheet1[Project],Sheet1[Setup Costs]),Sheet1[Setup Costs])

Both give you the 275 value you expect, but the second is a stronger option as it's dynamic based on the data. The first makes an assumption about the data structure and will break if it varies from that. Let me know if this works for you.

Thanks,

Ben

[mrothschild]

Thanks very much.  That worked.  I was also able to eventually set the "Visual Level Filter" to "Quarter is 1", and thought I had tried that initially before posing the query but wasn't able to "apply filter" - it had been greyed out.  

Thanks again!