cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrothschild Member
Member

Over-summing cumulative values in card visual?

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

 

ProjectSetup CostsQuarterSales
A5015
A50210
A50315
A50420
B10015
B100210
B100315
B100420
C125110
C125220
C125330
C125440
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
bhpage Regular Visitor
Regular Visitor

Re: Over-summing cumulative values in card visual?

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.
 
Cumulative Values.PNG
 
Thanks,
Ben
 
2 REPLIES 2
Highlighted
bhpage Regular Visitor
Regular Visitor

Re: Over-summing cumulative values in card visual?

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.
 
Cumulative Values.PNG
 
Thanks,
Ben
 
mrothschild Member
Member

Re: Over-summing cumulative values in card visual?

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!