CODEWITHSUNDEEP
excel-formulapowerbidaxpowerquery
Serdia
Serdia

Reputation: 4428

Value column counts or Summarizes even if I checked "Dont Summarize" Power BI Desktop

The goal is to create a visual that shows Budget vs Premium for each month in a year; Something like that:

enter image description here

I established relationship between Premiums table and Divisions table. It didnt give me any error, so I'm assuming everything is fine.

enter image description here

On a chart I need SUM of premium but NOT SUM of Budget. But when I put Budget on a column chart it summarizes it. I checked "Do not summarize" but it still, either summarize either count value.

Premium value summarized which is fine, that is what I need. But Budget value I do not need summarize or count.

enter image description here

Is it possible? Why it acting that way?

.PIBX file is available here: https://www.dropbox.com/s/6kvh7quylcirj0o/PremiumByDivisions1.pbix?dl=0

Upvotes: 0

Views: 3426

Answers (1)

Foxan Ng
Foxan Ng

Reputation: 7151

You have 6 InsurType and hence 6 Budget for each month.

That's why Power BI has to enforce a summarization to the Budget column when it's put in the Value of the chart.

To display Premium alongside Budget for each InsurType, you'll have to create a measure for each InsurType and add them to the chart:

e.g.

Commercial Auto Budget = 
CALCULATE(
    FIRSTNONBLANK('Divisions 2'[Budget], 0),
    'Divisions 2'[InsurType] = "Commercial Auto"
)

Worker's Comp Budget = 
CALCULATE(
    FIRSTNONBLANK('Divisions 2'[Budget], 0),
    'Divisions 2'[InsurType] = "Worker's Comp"
)

Specialty Casualty Budget = 
CALCULATE(
    FIRSTNONBLANK('Divisions 2'[Budget], 0),
    'Divisions 2'[InsurType] = "Specialty Casualty"
)

...

You also have to change the Cross filter direction between Premium and Division 2 table to Both so that the Date filter can be passed to the Division 2 table.

Results:

results

P.S. If you're OK with not putting Premium in the same chart, then you can just create one measure for Budget and then put in InsurType as Legend to the chart to separate the types. (Legend is only usable when you have one Value)

Upvotes: 3

Related Questions