User Profile
Axel_hnk
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
Re: JSON theme for visual "slicer" with new options in PBI Desktop Version 2022
Hi Johanna, I figured it out. You have to put in numbers as values. The following table shows all combinations: In order to use it here you would have to have this JSON (15 beeing all ticks): "header": [{ "show": true, "fontColor": { "solid": { "color": "#212121"}}, "background": { "solid": { "color": "#F4F4F4"}}, "outlineStyle": 15, "outlineColor": { "solid": { "color": "#205527" } }, "outlineWeight": 1, "textSize": 11, "bold": false, "fontFamily": "Lucida Sans Unicode" }] For this section you need that "items": [{ "fontColor": { "solid": { "color": "#212121"}}, "background": { "solid": { "color": "#F4F4F4"}}, "textSize": 12, "outlineStyle": 15, "padding": 10 }] I already created a ticket with microsoft with this and other missing documentation regarding JSON properties and their enumerations. Cheers, AxelPBI Theming JSON - Missing Properties
Hi there, I am heavily relying on theming via JSON. Here are some urls: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-report-themes https://github.com/MattRudy/PowerBI-ThemeTemplates But unfortunately for some time now I miss some attributes and I cannot get a hold of it how to define these. Example: matrix visual (pivot table) Column grand total Row grand total Example: slicer visual Values --> border postion (top, bottom, left, right) Another area are the already matured new shapes. The former attribute "shape" does not work with these anymore or many of the new fetures are not available to define, respectively. I hope someone can shed some light on the situation. Greatly appreciated. Thx, AxelSUMX Problem with Forecast % on Certain Level
Hi there, I basically want to calculate a forecast for a current MTD Value for a product till the end of the month. The problem: Although I am using SUMX / VALUES the aggregation/totals are not considering that. Due to uncertainties regarding past availablity I want to calculate the saisonality for the forecast on the combined level of Product_Category AND Product_Subcategory. A Product_Subcategory can be applied to multiple Product_Categories. Example: Product_Category_Product_Subcategory result: Current month - previous year total sales: 1000 Current month - previous year MTD sales: 150 ==> 15% saisononality percentage Product result: Current month - current year MTD value: 28 Forecast: 28 / 0.15 = 186.66 The forecast value is derived from a 3 year average of the previous years. The example shows the 1 year previous example calculation. Percentage_Measure = VAR MaxDate = MAX ( Date_Dax[Date] ) VAR EndOfMonthCurrent = MAX ( Date_Dax[Calendar EndOfMonth] ) VAR StartOfMonthPast1 = DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), 1 ) VAR EndOfMonthPast1 = EOMONTH ( StartOfMonthPast1, 0 ) VAR EndOfDatePast1 = IF ( //Check if endofmonth selection - relevant for Feb 29/28 MaxDate = EndOfMonthCurrent, //Calc the real end of month of the past year EOMONTH ( DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), 1 ), 0 ), //use the given date DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), DAY ( MaxDate ) ) ) //calculate the result for the whole month of the previous year VAR VolumeEomPast1 = CALCULATE ( SUM ( 'Sales'[Sales Value] ), Date_Dax[Date] >= StartOfMonthPast1 && Date_Dax[Date] <= EndOfMonthPast1, ALLEXCEPT ( Product_Table, Product_Table[Product_Category_Product_Subcategory] ) ) //calculate the result for the MTD value of the month of the previous year VAR VolumeMtdPast1 = CALCULATE ( SUM ( 'Sales'[Sales Value] ), Date_Dax[Date] >= StartOfMonthPast1 && Date_Dax[Date] <= EndOfDatePast1, ALLEXCEPT ( Product_Table, Product_Table[Product_Category_Product_Subcategory] ) ) // calculate the % - in our example 15% VAR VolumePercent1 = DIVIDE (VolumeMtdPast1, VolumeEomPast1) Here is what I'd like to see (forecast calculated on Category_Subcategory and then summed up to Category) : (The table looks a bit weird - the community forum of MS makes my tables always weird, although generated with the basic table tool) Product Category Product_Category& Subcategory Product Forecast Sales Value Percentage Cat_A 1125 175 16,5% Cat_ASubCat_A 1000 150 15% Prod_1 333,33 50 15% Prod_2 500 75 15% Prod_3 166,66 25 15% Cat_ASubCat_B 125 25 20% Prod_4 50 10 20% Prod_5 75 15 20% Cat_B 500 95 20% Cat_BSubCat_A 300 45 15% Prod_6 133,33 20 15% Prod_7 166,66 25 15% Cat_BSubCat_C 200 50 25% Prod_8 200 50 25% When I apply the follwing formula: Forecast_Measure = VAR VolumeAmount = Sales_Volume_MTD_Measure VAR AVG = Percentage_Measure // from above DIVIDE(VolumeAmount, AVG) I get this (not really astonishing) - calculation on top level with aggregated values - WRONG: Product Category Product_Category& Subcategory Product Forecast Sales Value Percentage Cat_A 1060,6 175 16,5% Cat_ASubCat_A 1000 150 15% Prod_1 333,33 50 15% Prod_2 500 75 15% Prod_3 166,66 25 15% Cat_ASubCat_B 125 25 20% Prod_4 50 10 20% Prod_5 75 15 20% Cat_B 475 95 20% Cat_BSubCat_A 300 45 15% Prod_6 133,33 20 15% Prod_7 166,66 25 15% Cat_BSubCat_C 200 50 25% Prod_8 200 50 25% So I need to use SUMX. I tried this: VAR VolumeAmount = Sales_Volume_MTD_Measure VAR AVG = Percentage_Measure // from above SUMX( VALUES(Product_Table[Product_Category_Product_Subcategory]), DIVIDE(VolumeAmount, AVG) ) But I got a very wrong result on the ALL_Category level (roughly something like the forecast sum fo all Subcategories multiplied by the number (count) of existing (even without Sales_Value) Subcategories. When I filter out a blank Subcategory the result changes as well (decrease). So I experimented with dozens of combination leading to either one of the results. It's like as SUMX is not doing it's job properly. I hope I could make my situation clear and you guys have an idea where I am wrong. Much appreciated, thanks!SUMX with Lookup
Hi there, I need to calculate the following: I have two tables (no relationship available): [Table_Percentage] - Not all ProductId available as Reference AND [Table_Sales] ProductIdDate is a concatenation of ProductId and DatekeySoM (SoM = StartOfMonth) What I want to achieve Selection Datatype A: Selection Datatype B: Question I want to calculate the SalesAmount% on the ProductIdDate level and need to apply a percentage from the "Table_Percentage" where both ProductIdDate levels match. If not null will be the result. On the aggregation level I need a strict sum of alle items calculated at the ProductIdDate level. The inital calculation can be something like this: SUMX( VALUES('Table_Sales'[ProductIdDate]), SUM('Table_Sales'[SalesAmount]) * [LookUpValue of 'Table_Percentage'] ) This is what I am looking for: [LookUpValue of 'Table_Percentage'] Can anybody help? Thx in advance
