Forum Discussion
Unwanted automatic aggregation on a Fact Column when pulling to Value field of line chart
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
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.
9 Replies
Check if the dropdown next to "Anzahl von anzahl_anschlüsse" has an option "Nicht zusammenfassen" or similar.
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.
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.
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.
It seems that you are using a Direct Query connection using someone else's dataset?