[IBCS] Advanced Power BI Tutorial - Native Column Chart
Рет қаралды 9,708
4 ай бұрын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
@PowerBIPark 4 ай бұрын
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
@svenjanning3047 Ай бұрын
Can you let me know, which video exactly?
@abediaz6707 3 ай бұрын
I am nerding out so much about this! Thank you for sharing this!
@fsanfo 4 ай бұрын
Awesome as usual! Thanks for sharing!
@Fannie2uN2010 4 ай бұрын
Excellent work
@aoianalytics 3 ай бұрын
Great Video!!!
@arbazahmad7177 4 ай бұрын
Excellent🎉 thanks for sharing...
@PowerBIPark 4 ай бұрын
Thank you! Cheers!
@DeronHuskey 4 ай бұрын
Beautiful! Very ingenious.
@PowerBIPark 4 ай бұрын
Do you prefer the column charts or the bar charts this way?
@DeronHuskey 4 ай бұрын
@@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!
@PowerBIPark 4 ай бұрын
@@DeronHuskey Oh I meant more like, do you prefer this as vertical or horizonal (column vs bar charts), but I'll take it :)
@DeronHuskey 4 ай бұрын
@@PowerBIPark Ah, gotcha. I haven't seen the bar chart video yet. Let me take a look.
@DeronHuskey 4 ай бұрын
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?
@SoukainaChakour 3 ай бұрын
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 😢
@miftahrizky 4 ай бұрын
May Value is correct?
@mohammedjaveed8643 4 ай бұрын
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?
@PowerBIPark 4 ай бұрын
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
@Gerschk 4 ай бұрын
Hi Injae! How would I go about changing the format for the deltaPY (middle error bar) i.e. from 0.04bn to 38.9M
@PowerBIPark 4 ай бұрын
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])
@Gerschk 4 ай бұрын
worked as expected!@@PowerBIPark
@user-kb1yb1dw5j 4 ай бұрын
for the "_3 title color top", you have ">" for both conditions... is that correct? (at 9:32)
@PowerBIPark 4 ай бұрын
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
@Gerschk 4 ай бұрын
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...
@PowerBIPark 4 ай бұрын
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
@Gerschk 4 ай бұрын
I'll try the disconnected switch table as there are some samples of them in your previous videos@@PowerBIPark
@PowerBIPark 4 ай бұрын
Sounds good, let me know how it goes!
@Gerschk 4 ай бұрын
Seems like the disconnected switch table is working well so far! =)@@PowerBIPark
@AliAlSayer 4 ай бұрын
I’ve latest version of power bi but I didn’t have Dax view ?
@PowerBIPark 4 ай бұрын
Go to your options, preview features, and there should be an option to turn it on
@dssmatus4962 4 ай бұрын
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
@PowerBIPark 4 ай бұрын
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
@dssmatus4962 4 ай бұрын
@@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 ;)
@MeriemGS 2 ай бұрын
@@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])
@MeriemGS 2 ай бұрын
@martinbubenheimer6289 4 ай бұрын
Great visualization! I just wonder, why did you make a -43% column much longer than a +73% column? Shouldn't they be proportional?
@PowerBIPark 4 ай бұрын
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
@martinbubenheimer6289 4 ай бұрын
@@PowerBIPark OK, makes sense: The length represents the absolute value and the label the percentage.
@PowerBIPark 4 ай бұрын
Yep. Ofc it would be quite easy to put it at the flat% length, but I think it would add confusion the other way 😊
@PowerBIPark 4 ай бұрын
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
@shubham6565 3 ай бұрын
Delta Py Line is not parallel to x- axis. Could you please help? 12:17
@PowerBIPark 3 ай бұрын
Are you using different data?
@shubham6565 3 ай бұрын
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".
@PowerBIPark 3 ай бұрын
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 😅
@shubham6565 3 ай бұрын
@@PowerBIPark Thanks.. Will try it..😁😁
TheSoul Music Jam
Рет қаралды 23 МЛН
Poly Holy Yow Spanish
Рет қаралды 28 МЛН
Fernan
Рет қаралды 2,5 М.
MITutorials
Рет қаралды 938
Havens Consulting
Рет қаралды 4,5 М.
MaxxPC
Рет қаралды 1,9 МЛН
GStore Mobile
Рет қаралды 2,2 МЛН
MAMU TECH
Рет қаралды 264 М.