Unwanted automatic aggregation on a Fact Column when pulling to Value field of line chart

[Anonymous]

Dear community,

I am rather new to Power BI. I have a star schema with 3 Dimensions (Date-Dimension, "Netzbetreiber"-Dimension - which is kind of a customer - and the "AnschlussTyp-Dimension, which is kind of a ProductType). My Fact-Table has Foreign Keys to these 3 tables and 2 facts. One fact is telling how many product instances of a given "ProductType" a "customer" had on a certain date.

My idea was to have a line chart that shows one line per combination of Customer and ProductType so that you can see how the fact changed over the time. This means that it needs to use the fact value "as-is" and not do any calculation/aggregation.

Please have a look at the attached screenshot. I tried to mark all things of importance there.

Anyway, I couldn't get that far, because as soon as I pull the fact column ("anzahl_anschluesse") over to the "Value" field (German: "Werte"), Power BI applies the aggregation "Anzahl von" which translates to "Count" or "Number Of" - even though I configured the "anzahl_anschluesse" field in the header ribbon to not use any aggregation (German: "Zusammenfassung").

By clicking on the pulled fact column in "Werte", I could change the kind of aggregation, but there seems to be no way to tell that I do not want any aggregation at all. If you look at the table visual in the left upper corner, you can see most of the raw data, especially the actual combinations of Customer and ProductType with the correct numbers.

However, in my line chart visual, it seems that I have no chance to get rid of the automatic aggregation and it always counts the number of different values that are present for a Customer / ProductType combination in a month, which varies from 2 to 4 as you can see.

Question 1: How can I get rid of the automatic aggregation?

The follow-up question 2 would be, how I can achieve my main goal to have a line in the line chart for each Customer / ProductType combination with the month on the x-axis?

Many thanks in advance,

Stephan

[lbendlin]

Use Minimum or Mittelwert. 

This is a measure, so it depends on the column grouping in the row area of your matrix visual. Rewrite the measure if needed.

[Anonymous]

I'n not sure if I understand what you mean. The "anzahl_anschluesse" and "anzahl_kunden" columns are pure fact columns provided in the fact table of the star-schema. According to my understanding, a "measure" is something that is calculated in Power BI from other facts or measures. I do not need any calculation here, I would just like to see the fact value as it is in the fact table.

Maybe the whole problem is related to my 2nd question, because the fact table of my starschema relies on 3 dimensions and in the line chart visual and most other visuals, I can only apply 2 dimensions: The date dimension as the x-axis and either the "customer" dimension ("dt_netzbetreiber") OR the producttype dimension ("dt_anschluss_typ"). So my assumption / hope is, that if I could only find a way to draw e.g. lines for each combination of customer and producttype in ONE line chart visual then Power BI would hopefully stop asking for some calculation. But I only manage to configure the visual to draw lines either for date+customer or date+producttype, like e.g. shown in the screenshot below. And I don't want to use the "Small multiples". I want to have all the lines in the same diagram. 

[lbendlin]

It seems that you are using a Direct Query connection  using someone else's dataset?

[Anonymous]

Yes, Direct Query, but the data model is still "work in progress" so I could change that as well if needed. But the business requirement is to show a graph with the DAILY values of "anzahl_anschluesse" for all combinations of Netzbetreiber and Anschlusstyp, as it is shown partly in the table visual on the 1st screenshot.

Or is it the only way to create an artificial dimension called "Netzbetreiber_Anschlusstyp" which contains all the combinations, so that technically, the number of dimension gets reduced to only 2 dimensions?

[lbendlin]

That might work too.  Running measures (or even calculated columns, with a local model copy)  against Power BI Dataset data sources will always be tricky.  If possible push your transforms upstream.

[Anonymous]

Let me try it maybe one last time:

Is it possible to set up a line chart (or any other visual) with 3 Dimensions?:

a) X-Axis: Date-Dimension

b) Value: Fact from Fact-Table

c) Each drawn line represents a combination of the Customer and ProductType.

e.g. Customer C1 had 100 instances if ProductType P1, 200 instances of ProductType P2 on 2022-01-01 and some other values in the course of the year

and Customer 2 had 150 instances if ProductType P1, 250 instances of ProductType P2 on 2022-01-01 and some other values in the course of the year.

So I want to have 4 lines: 

- 1 x the line with the daily values of Customer C1/ProducType P1

- 1 x the line with the daily values of Customer C1/ProducType P2

- 1 x the line with the daily values of Customer C2/ProducType P1

- 1 x the line with the daily values of Customer C2/ProducType P2

Therefor, I suppose I would have to be able to add both Customer.name and ProductType.name to the "Legende", but only one item seems to be possible there. Or is there another way?

Many thanks again!

Stephan

[lbendlin · Accepted solution]

You generally have two choices

1. use the legend for the bucketing (separate lines) - the legend needs to be a column, and you can have only one measure in the value well.

2. do NOT use a legend, instead use separate measures in the value well, one per line.

[Anonymous]

Thanks for your help! As you can see in the screenshot below, there are various options in that dropdown like "Summe", "Mittelwert" etc. and it seems that one of those calculations MUST be selected. Even the fact that the Submenu "Werte anzeigen als" is already set to "Keine Berechnung", PowerBI uses the Anzahl. If you any more ideas, I'd be grateful if you let me know.

[lbendlin]

Check if the dropdown next to "Anzahl von anzahl_anschlüsse" has an option "Nicht zusammenfassen" or similar.