[IBCS] Advanced Power BI Tutorial - Native Column Chart
HTML-код
- Опубликовано: 26 фев 2024
- Get a 3 for 1 deal on Charts in Power BI
::::::::::: Links :::::::::::::::
🔥 IBCS style Matrix Tutorial: • NATIVE Power BI Table ...
🔥 IBCS Template: www.ibcs.com/resource/multi-t...
🔥 PBI Core Visual Update: www.linkedin.com/pulse/additi...
::::::::::: Data :::::::::::::::
Month Number , Month , 2022 , 2023 ,2023 Predictions
1 , January , 2825.00 , 7140.00 ,
2 , February , 5005.00 , 1614.00 ,
3 , March , 3612.00 , 14752.00 ,
4 , April , 2978.00 , 933.00 ,
5 , May , 8662.00 , 6343.00 ,
6 , June , 4750.00 , 8223.00 ,
7 , July , 4433.00 , 6953.00 ,
8 , August , 2062.00 , 9041.00 ,
9 , September , 9329.00 , 10992.00 ,
10 , October , 16243.00 , 9275.00 ,
11 , November , 4011.00 , , 9690.10
12 , December , 17885.00 , , 8483.35
:::::::::: Measures ::::::::::::
DEFINE
MEASURE 'IBCS Data'[_5 Delta PY] = IF(ISBLANK([_1 FC]), [_1 AC] - [_1 PY], [_1 FC] - [_1 PY])
MEASURE 'IBCS Data'[_2 Max value] = IF(ISBLANK([_1 FC]),
if([_1 AC]□[_1 PY], [_1 AC], [_1 PY]),
if([_1 PY]□[_1 FC], [_1 PY], [_1 FC]))
MEASURE 'IBCS Data'[_1 FC] = SUM('IBCS Data'[2023 Predictions])
MEASURE 'IBCS Data'[_1 PY] = SUM('IBCS data'[2022])
MEASURE 'IBCS Data'[_1 AC] = SUM('IBCS data'[2023])
MEASURE 'IBCS Data'[_6 Delta PY %] = DIVIDE ( [_5 Delta PY], [_1 PY] )
MEASURE 'IBCS Data'[_5 Delta PY Line] = [_4 ACPY MAX] * 2.3
MEASURE 'IBCS Data'[_5 Delta PY Line Positive AC] = IF(ISBLANK([_1 FC]),IF([_5 Delta PY] □0, [_5 Delta PY Line] + [_5 Delta PY], blank()))
MEASURE 'IBCS Data'[_5 Delta PY Line 2] = [_5 Delta PY Line]
MEASURE 'IBCS Data'[_5 Delta PY Line Negative AC] = IF(ISBLANK([_1 FC]),
IF([_5 Delta PY] ●0, [_5 Delta PY Line] + [_5 Delta PY], blank())
)
MEASURE 'IBCS Data'[_6 Delta PY % Line] = [_4 ACPY MAX] * 3.8
MEASURE 'IBCS Data'[_6 Delta PY % Line 2] = [_6 Delta PY % Line]
MEASURE 'IBCS Data'[_6 Delta PY % Line Positive AC] = IF(ISBLANK([_1 FC]),
IF([_6 Delta PY %] □0, [_6 Delta PY % Line] + [_6 Delta PY %]*[_7 Percentage Bar Modifier], blank()))
MEASURE 'IBCS Data'[_6 Delta PY % Line Negative AC] = IF(ISBLANK([_1 FC]),IF([_6 Delta PY %] ● 0, [_6 Delta PY % Line] + [_6 Delta PY %]*[_7 Percentage Bar Modifier], blank()))
MEASURE 'IBCS Data'[_3 title color top] = IF(ISBLANK([_1 FC]),
if([_5 Delta PY] □ 0, "#000000", "#B3B3B3"),
if([_5 Delta PY] □ 0, "#118DFF", "#B3B3B3"))
MEASURE 'IBCS Data'[_3 title color bot] = IF(ISBLANK([_1 FC]),
if([_5 Delta PY] ● 0, "#000000", "#B3B3B3"),
if([_5 Delta PY] ● 0, "#118DFF", "#B3B3B3"))
MEASURE 'IBCS Data'[_6 Delta PY % Line FC] = IF(ISBLANK([_1 FC]),blank(), [_6 Delta PY % Line] + [_6 Delta PY %]*[_7 Percentage Bar Modifier])
MEASURE 'IBCS Data'[_5 Delta PY Line Negative FC] = IF(ISBLANK([_1 FC]),blank(),
IF([_5 Delta PY] ●0, [_5 Delta PY Line] + [_5 Delta PY])
)
MEASURE 'IBCS Data'[_5 Delta PY Line 3] = [_5 Delta PY Line]
MEASURE 'IBCS Data'[_6 Delta PY % Line 3] = [_6 Delta PY % Line]
MEASURE 'IBCS Data'[_7 Delta PY % Vals] = IF([_6 Delta PY %] ● 0,[_6 Delta PY % Line]* 0.92 + [_6 Delta PY %]*[_7 Percentage Bar Modifier], [_6 Delta PY % Line] + [_6 Delta PY %]*[_7 Percentage Bar Modifier])
MEASURE 'IBCS Data'[_5 Delta PY Line Postivie FC] = IF(ISBLANK([_1 FC]),blank(),
IF([_5 Delta PY] □0, [_5 Delta PY Line] + [_5 Delta PY])
)
MEASURE 'IBCS Data'[_7 Delta PY % label] = DIVIDE ( [_5 Delta PY], [_1 PY] )*100
MEASURE 'IBCS Data'[_4 ACPY MAX] = MAXX(ALLSELECTED('IBCS Data'), [_2 Max value])
MEASURE 'IBCS Data'[_4 Y Axis Max] = [_4 ACPY MAX] * 4.8
MEASURE 'IBCS Data'[_2 Min value] = IF(ISBLANK([_1 FC]),
if([_1 AC]●[_1 PY], [_1 AC], [_1 PY]),
if([_1 PY]●[_1 FC], [_1 PY], [_1 FC]))
MEASURE 'IBCS Data'[_7 Delta PY % label Color] = IF(ISBLANK([_1 FC]),
if([_5 Delta PY] □ 0, "#3C931A", "#D64550"), "#118DFF")
MEASURE 'IBCS Data'[_7 Percentage Bar Modifier] = DIVIDE( [_4 ACPY MAX], MAXX(ALLSELECTED('IBCS Data'), [_6 Delta PY %]))*0.7 
Наука
![[IBCS] New Column chart in Power BI - Feb 2024 Update](/img/1.gif)
In the video, for the Percent Bars - I was using Flat change and just adding a Percent Label.
The measures in the description are now updated to show the length of the Percent, by using a modifier measure (_7 Percentage Bar Modifier)
I hope this will help everyone to get better percents, although you may still need to change this value if you have something like 400% changes
Can you let me know, which video exactly?
I am nerding out so much about this! Thank you for sharing this!
Awesome as usual! Thanks for sharing!
Excellent work
Great Video!!!
Beautiful! Very ingenious.
Do you prefer the column charts or the bar charts this way?
@@PowerBIPark The information density is what stands out for me. Stakeholders can see at a glance how we are performing, so yes, I prefer them this way. I'm going to try my hand at it this weekend. Thanks for the demonstration!
@@DeronHuskey Oh I meant more like, do you prefer this as vertical or horizonal (column vs bar charts), but I'll take it :)
@@PowerBIPark Ah, gotcha. I haven't seen the bar chart video yet. Let me take a look.
Hard to choose between those two. Great work.
As I was watching the bar chart video, I noticed that the delta PY differences were a little more pronounced...the April and May bars are both 2k, but May is a little longer. Same goes for June/July. My assumption is that's a function of the Units chosen for display.
Would it be better to show an unrounded number there?
Excellent🎉 thanks for sharing...
Thank you! Cheers!
Love your videos and explanation. Could you please do another example where we would have to compare more than 2 series on each month (example : AC vs PY vs Budget) ? I’m stuck on that 😢
May Value is correct?
Hi Injae! How would I go about changing the format for the deltaPY (middle error bar) i.e. from 0.04bn to 38.9M
So this isn't really possible right now as is, because the error bars unfortunately don't have the formatting option to change display units.
Honestly, if it was a make or break situation, I would use the same steps that I do to make the transparent bars for the relative % bars, and modify the values there.
You would need to use this:
Delta PY Vals = IF([_5 Delta PY] < 0,[_5 Delta PY Line]* 0.92 + [_5 Delta PY], [_5 Delta PY Line] + [_5 Delta PY])
worked as expected!@@PowerBIPark
Hello - For the % bars, how do we show the value as %. At the moment it does not show how to go from 153 to 153% at the top?
Hey, I modified the code to change the bars to percent length already, it's whats in the pinned comment.
If you want to see how it works, you can check out my other video showing IBCS bars, where I show it more in detail
Hi Injae! How feasible do you think it would be to include Field parameters for AC and PY? For example, from Actual Current Gross Sales to Actual Current Net sales, etc...
You'll have to modify the formula for ac and py to be what you select, but honestly should be bo problem. Sometimes you can run into trouble with field parameters when choosing measures, so you could do a disconnected switch table or calc groups to the same effect
I'll try the disconnected switch table as there are some samples of them in your previous videos@@PowerBIPark
Sounds good, let me know how it goes!
Seems like the disconnected switch table is working well so far! =)@@PowerBIPark
for the "_3 title color top", you have ">" for both conditions... is that correct? (at 9:32)
Yeah, because it's the "top" Label, regardless of if it is in FC period or not, and Delta py already takes that into account
Great visualization! I just wonder, why did you make a -43% column much longer than a +73% column? Shouldn't they be proportional?
Actually an excellent question. The percentage is calculated as Delta change over PY, so for each month it's taking different comparative values - this is by design. It's also important to see that we are using the past year percent not the percent difference of grand total change 😊 hope that helps
@@PowerBIPark OK, makes sense: The length represents the absolute value and the label the percentage.
Yep. Ofc it would be quite easy to put it at the flat% length, but I think it would add confusion the other way 😊
So I had some discussions, and I've decided to update the code so the bar shows %. Thanks for bringing this up, I'm simply going to update the code
Hey Park, this is simply amazing! A question - for a measure _4 ACPY MAX, what would be your suggestion if we have a dataset with granular data per each day, instead of scalar value per year as you have? Kind of struggling to define the measure to get the total sum per all visible years
hmmm, I'm not 100% sure, but if you have granular data for the day, could you not use "MAXX(Allselected(day))"? Maybe that could work :) You just need to replace years with the column you're using for the x axis
@@PowerBIPark Thanks a lot for a reply. Well, I have there a time hierarchy (Era/Year/Quarter/Month) instead of singular year or date field. With you original code I am returning the biggest "transaction" in whole table per each period, with suggestion to return ALLSELECTED over calendar table it has unique value per filtered period....so not there yet, need to dig in a little deeper it seems...thanks anyway ;)
@@PowerBIPark Hi , I replaced the column All Selected with my Date table to (Month Number) and the X-axis to Monat Nummer (Month Number )then it worked fine :) btw thanks for this wonderful video
here is the formula that might help @dssmatus4962 :
_4 ACPY MAX = MAXX(ALLSELECTED(Kalendar[Monat Nummer]), [_2 Max value])
I’ve latest version of power bi but I didn’t have Dax view ?
Go to your options, preview features, and there should be an option to turn it on
Delta Py Line is not parallel to x- axis. Could you please help? 12:17
Are you using different data?
Yes,
My "_4 ACPY MAX" measure is not taking the maximum value of chart, instead it is equal to the value of "_2 Max value".
That's because that's how MAXX works. The simplest way is to make a table of unique values of month name and number, make a relationship with month number to the original table, and use that tables as the first argument.
The other uses a bit more dax I won't write from my phone 😅
@@PowerBIPark Thanks.. Will try it..😁😁