Help with Formatting a Matrix with Multiple Hierarchical Levels in Power BI
Hello everyone, I am working on a Power BI report that uses a matrix with 10 hierarchical levels to display my financial data. My goal is to create a P&L (Profit and Loss) presentation where data at lower levels aggregates correctly to higher levels. However, I am facing a sorting issue: for example, I find the "Profit Before Tax" appearing at the top, while I would like to reorganize it so that it fits properly within the structure of my P&L (exemple here) Here are a few specific points where I would appreciate your help: Custom Sorting: How can I define a specific sort order for my hierarchical levels so that they display in the desired order? Data Aggregation: Is there a method to ensure that values aggregate correctly at each hierarchical level? Matrix Formatting: What are the best practices for formatting a matrix with so many levels to ensure optimal readability? Thank you in advance for your help and suggestions. Any advice or resources would be greatly appreciated! Best regards,
Problem with formatting and series
Hi everyone, I'm developing a PowerBI custom visual to draw time series differentiated by a legend. The problem I'm having is with formatting settings. What I would like is to have a colorPicker in my formatting pane for each of my series. Here's what I've already done: - I created a "colorSelector" object in capabilities.json that will be a Slice for each series colorPicker - I created a selection for each series using this documentation (Create selections for series) - I used colorHelper to set the default color for series with colorPalette or find the color of the “colorSelector” object associated with a serie, based on this documentation - I populated the pane formatting based as much as possible on the barChat example github I manage to get something but the behavior is bizarre: - There's a colorPicker for each different series BUT when I change the color, it instantly reverts to its default value. I think I'm missing some information so that I can properly assign a color to each series and then retrieve them, but I haven't been able to find a solution for several days. Here's what I think The colorHelper can't find the color associated to the object but I tried to find the color in a manual way and there is the same result There is a problem with the selection, maybe I'm doing it wrong? populateColorSelector is not implemented properly? I'm not creating the data properly? (dataSeries) I'd like to thank anyone who can help me understand the problem I encounter. Here is the code: capabilities.json { "dataRoles": [ { "displayName": "Categories", "name": "category", "kind": "Grouping" }, { "displayName": "Measures", "name": "measure", "kind": "Measure" }, { "displayName": "Series", "name": "series", "kind": "Grouping" } ], "objects": { "colorSelector": { "properties": { "fill": { "type": { "fill": { "solid": { "color": true } } } } } } }, "dataViewMappings": [ { "categorical": { "categories": { "for": { "in": "category" } }, "values": { "group": { "by": "series", "select": [{ "for": { "in": "measure" } } ] } } } } ], "privileges": [] } settings.ts: "use strict"; import { formattingSettings } from "powerbi-visuals-utils-formattingmodel"; import { dataSerie } from "./visual"; import FormattingSettingsCard = formattingSettings.SimpleCard; import FormattingSettingsSlice = formattingSettings.Slice; import FormattingSettingsModel = formattingSettings.Model; import ColorPicker = formattingSettings.ColorPicker; class ColorSelectorCardSettings extends FormattingSettingsCard { name: string = "colorSelector"; displayName: string = "Data Colors"; slices: FormattingSettingsSlice[] = []; } export class VisualFormattingSettingsModel extends FormattingSettingsModel { colorSelector = new ColorSelectorCardSettings(); cards: FormattingSettingsCard[] = [this.colorSelector]; populateColorSelector(dataPoints: dataSerie[]) { const slices: FormattingSettingsSlice[] = this.colorSelector.slices; if (dataPoints) { dataPoints.forEach(dataPoint => { slices.push(new ColorPicker({ name: "fill", displayName: dataPoint.value.toString(), value: { value: dataPoint.color }, selector: dataPoint.selection.getSelector(), })); }); } } } visual.ts: "use strict"; import powerbi from "powerbi-visuals-api"; import { FormattingSettingsService } from "powerbi-visuals-utils-formattingmodel"; import { ColorHelper } from "powerbi-visuals-utils-colorutils"; import "./../style/visual.less"; import VisualConstructorOptions = powerbi.extensibility.visual.VisualConstructorOptions; import VisualUpdateOptions = powerbi.extensibility.visual.VisualUpdateOptions; import IVisual = powerbi.extensibility.visual.IVisual; import ISelectionId = powerbi.visuals.ISelectionId; import IVisualHost = powerbi.extensibility.visual.IVisualHost; import IColorPalette = powerbi.extensibility.IColorPalette; import DataViewValueColumns = powerbi.DataViewValueColumns; import DataViewValueColumnGroup = powerbi.DataViewValueColumnGroup; import DataViewObjectPropertyIdentifier = powerbi.DataViewObjectPropertyIdentifier; import ILocalizationManager = powerbi.extensibility.ILocalizationManager; import { VisualFormattingSettingsModel } from "./settings"; export interface dataSerie { value: powerbi.PrimitiveValue; selection: ISelectionId, color: string; } export class Visual implements IVisual { private formattingSettings: VisualFormattingSettingsModel; private formattingSettingsService: FormattingSettingsService; private host: IVisualHost; private colorPalette: IColorPalette; private localizationManager: ILocalizationManager; constructor(options: VisualConstructorOptions) { this.host = options.host; this.localizationManager = this.host.createLocalizationManager(); this.formattingSettingsService = new FormattingSettingsService(this.localizationManager); this.colorPalette = options.host.colorPalette; } public update(options: VisualUpdateOptions) { this.formattingSettings = this.formattingSettingsService.populateFormattingSettingsModel(VisualFormattingSettingsModel, options.dataViews?.[0]); const dataSeries: dataSerie[] = []; const series: powerbi.DataViewValueColumnGroup[] = options.dataViews[0].categorical.values.grouped(); const valueColumns: DataViewValueColumns = options.dataViews[0].categorical.values, grouped: DataViewValueColumnGroup[] = valueColumns.grouped(), defaultDataPointColor: string = "green", fillProp: DataViewObjectPropertyIdentifier = { objectName: "objectName", propertyName: "propertyName" }; series.forEach((ser: powerbi.DataViewValueColumnGroup, index) => { // create selection id for series const seriesSelectionId = this.host.createSelectionIdBuilder() .withSeries(options.dataViews[0].categorical.values, ser) .createSelectionId(); // get the color from series const defaultDataPointColor: string = this.colorPalette.getColor(ser.name.toString()).value; let colorHelper: ColorHelper = new ColorHelper( this.colorPalette, fillProp, defaultDataPointColor); let grouping: DataViewValueColumnGroup = grouped[index]; let color = colorHelper.getColorForSeriesValue(grouping.objects, grouping.name); // create the series elements dataSeries.push({ value: ser.name, selection: seriesSelectionId, color: color }); }); // Populate the formatting pane this.formattingSettings.populateColorSelector(dataSeries); } public getFormattingModel(): powerbi.visuals.FormattingModel { return this.formattingSettingsService.buildFormattingModel(this.formattingSettings); } } Sample of data: X Y Legend samedi 30 décembre 2023 14022,91642 C dimanche 31 décembre 2023 0,551076646 A dimanche 31 décembre 2023 10435,26002 C lundi 1 janvier 2024 0,689471555 A lundi 1 janvier 2024 12759,20759 C mardi 2 janvier 2024 0,305575341 A mardi 2 janvier 2024 10230,89312 C mercredi 3 janvier 2024 0,587809939 A mercredi 3 janvier 2024 11722,69859 C jeudi 4 janvier 2024 0,964788095 A jeudi 4 janvier 2024 13221,84671 C vendredi 5 janvier 2024 0,721572695 A vendredi 5 janvier 2024 11472,99755 C samedi 6 janvier 2024 0,394463782 A samedi 6 janvier 2024 43840,72009 B samedi 6 janvier 2024 11982,3041 C samedi 6 janvier 2024 515,2744629 D samedi 6 janvier 2024 694,6464676 E dimanche 7 janvier 2024 0,359213967 A dimanche 7 janvier 2024 46322,50275 B dimanche 7 janvier 2024 14065,89778 C dimanche 7 janvier 2024 279,9718025 D dimanche 7 janvier 2024 653,0402894 E And how I fill the data fields:How can I add Groups using Formatting model utils to build a formatting settings model?
Hi there! I use Formatting model utils to build a formatting settings model to populate the property panes. There are models, cards, and slices. I have relations as "Model -> Cards -> Slices". I want to add Groups and then it should look like "Model -> Cards -> Groups -> Slices". I found some information about the Formatting group here and I found an example of how to generate groups using "getFormattingModal" here. Then I should use getFormattingModal for all my formatting settings and I don't want it, because it is not convenient. So, how can I add groups to the formatting model using "Formatting group" instead of "getFormattingModal"? I need something like this:
Using JSON to Format Text Boxes
I'm running into an issue when trying to edit the JSON file for my powerBI report. I have a solid file, however, I am unable to edit the formatting for text boxes in the file. I found a solution from 2021. However, it seems to no longer work with the 8/2023 version of PowerBI. Would anyone have a code snippet they are willing to share? "textbox": { "*": { "*": [ { "fontSize": 14, "fontface": "Arial", "color": "#333333" } ], }}
After adding calculated group, my Calendar by Datanu visual lost formatting.
I was using Calendar by Datanu, to visualize data and it was working perfectly. After I added calculated measure group and changed formatting, my formatting broke for all visuals, however, with dax, I was able to only format specific text measures. And my usual measures are formatted fine in usual visuals, however, I am also using custom visual of calendar by Datanau and there formatting isn't working. Why did it change and how can I make it work. Same measure in matrix view is formatted fine, I am using dynamic formatting and "#,###"
Calendar Visual by MAQ Software: Highlighting today issue
Hi, I have a calendar page with 2 calendar by MAQ visuals. Both visuals use the same data but with different filter conditions. Today is May 17th, and on the top visual, the box for 17th May is not highlighted but on the bottom visual, today's box is highlighted yellow. How can I get this to be done across all calendar by MAQ visuals?
Custom Visual - TextInput in Visual Format Setting does not save value and reverts to default
Here's what happens: When I go to Visualizations->Format Visual->Visual, I have a textbox with default text in it. If I enter new text and hit enter, nothing happens. As soon as I click outside of the textbox the default value returns. Can anyone explain what's happening? Here is what I think are the relevent sections of code: From Capabilities.json "objects": { "redirectURL": { "displayName": "Redirect", "properties": { "value": { "displayName": "Hyperlink", "type": { "text": true } } } } } From Settings.ts class DataPointCardSettings extends FormattingSettingsCard { redirectURL = new formattingSettings.TextInput({ "placeholder": "Hyperlink", "name": "Hyperlink", "value": "Enter URL Here" }); name: string = "redirection"; displayName: string = "Redirection"; slices: Array<FormattingSettingsSlice> = [this.redirectURL]; } export class VisualFormattingSettingsModel extends FormattingSettingsModel { // Create formatting settings model formatting cards dataPointCard = new DataPointCardSettings(); cards = [this.dataPointCard]; } From visual.ts import { FormattingSettingsService } from "powerbi-visuals-utils-formattingmodel"; import { VisualFormattingSettingsModel } from "./settings"; ... export class Visual implements IVisual { private formattingSettings: VisualFormattingSettingsModel; private formattingSettingsService: FormattingSettingsService; ... constructor(options: VisualConstructorOptions) { ... this.formattingSettingsService = new FormattingSettingsService(); ... } public update(options: VisualUpdateOptions) { this.formattingSettings = this.formattingSettingsService.populateFormattingSettingsModel(VisualFormattingSettingsModel, options.dataViews); } public getFormattingModel(): powerbi.visuals.FormattingModel { return this.formattingSettingsService.buildFormattingModel(this.formattingSettings); } }Colorizing/formatting a line up to today's date
I want to color the line of a chart up to a defined date. The rest of the line should be dashed. For this I have already created two lines and superimposed them to format them differently. However, the end of the first line is disturbing, because a horizontal line is created. Is it possible to hide the tail? or to format the same line to defined data sections?How to make conditional number format depending on data fields
I like to create a matrix visual from a dataset like this: Month Value Target Operator Unit 2022-01 10 5 >= seconds 2022-02 0,53 0,4 <= % ... In my matrix visual, numbers should be representet with their associated number format (unit) and colored depending whether in target or not using the operator so in the end it should look like that: Month Value 2022-01 10secs 2022-02 53% ... How can I achieve that, using some kind of conditional formatting. I need a rule for different number formats (like seconds, hours, percent, decimal, date, Euros, Dollars etc.) and operator (>= and <=)
