Using DAX to Create a dynamic horizontal line for a bar or line chart in Power BI

user_15587184 0
Viewed 88

Hello Lovely People of SO,

I hope you guys are having a great day!

I have the following dataset

REGION ID STATUS
DC 1 NEW
ED 2 NEW
FR 3 OLD
FR 4 NEW
GI 5 OLD
GI 6 OLD
GY 7 NEW
GY 8 OLD
GY 9 OLD
GY 10 OLD
GY 11 OLD
GY 12 NEW
RT 13 NEW
TX 14 NEW
TX 15 NEW

I will first want to know how to use DAX to calculate the percentage of ID with STATUS ="OLD" by REGION

Intuitivatly in Python I can group by REGION and then summarize the number of OLD and NEW STATUS but here in PBI DAX things are not somewhat stright forward yet for me since I am learning so thanks you for helping my out with that, my main goal is to create a bar chart that will show the percentage of OLD STATUS by REGION and add a horizontal line that will display the global average of percentages, this is my own data summary

enter image description here

and my desired viz would look like this:

enter image description here

but I have no clue was to how to do that in dax in power bi thank you so much if you guys can help me out If you can reference some code online to do this or if there is a special bulti-in function to calculate this horizontal line thanks a million I will be super attentive to all of your comments

dax powerbi
3 Answers

One way to get the average for OLD is to create a calculated column for %. Then use DAX to find the average of just the ones with OLD status, using:

CALCULATE(AVERAGE([% column]), FILTER(table name, STATUS = “OLD”))

user_197033660

and let me know if It works for you:

OldPercent_Measure =
VAR TblSummary =
    ADDCOLUMNS (
        SUMMARIZE ( YourTable, YourTable[REGION] ),
        "STATUS OLD",
            CALCULATE ( COUNTROWS ( YourTable ) + 0, YourTable[STATUS] = "OLD" ),
        "TOTAL", CALCULATE ( COUNTROWS ( YourTable ) ),
        "% OLD",
            ROUND (
                DIVIDE (
                    CALCULATE ( COUNTROWS ( YourTable ) + 0, YourTable[STATUS] = "OLD" ),
                    CALCULATE ( COUNTROWS ( YourTable ) )
                ),
                2
            )
    )
VAR PercentOld =
    SUMX ( TblSummary, [% OLD] )
VAR GlobalAverage =
    AVERAGEX ( TblSummary, [% OLD] )

RETURN
    PercentOld

Then create a column chart, put [REGION] column in X_axis, and put OldPercent_Measure in the Y_axis[Values field]. I hope It solves your problem.

To calculate Global Average, the same code above. The only difference is to replace PercentOld with GlobalAverage after "RETURN" statement. Like This:

...... ...... RETURN GlobalAverage

Global Average

Extra Info: You want to see the result of your summary table:

EVALUATE
VAR TblSummary =
    ADDCOLUMNS (
        SUMMARIZE ( YourTable, YourTable[REGION] ),
        "STATUS OLD",
            CALCULATE ( COUNTROWS ( YourTable ) + 0, YourTable[STATUS] = "OLD" ),
        "TOTAL", CALCULATE ( COUNTROWS ( YourTable ) ),
        "% OLD",
            ROUND (
                DIVIDE (
                    CALCULATE ( COUNTROWS ( YourTable ) + 0, YourTable[STATUS] = "OLD" ),
                    CALCULATE ( COUNTROWS ( YourTable ) )
                ),
                2
            )
    )
VAR PercentOld =
    SUMX ( TblSummary, [% OLD] )
RETURN
    TblSummary

Resulting Screen:

FFFFFFFFF04

To find out how to put average line: You need to go analytics pane. Here is a good link to do that: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane

FFFd

user_194690880

The graph is a cummulative histogramm (the second left to right, top line). The line is a constant line at analytics. I used the same measure for value there. Have a nice day.

Rate = 
VAR Regions = VALUES(tbl[REGION])
VAR sumOfRates=
        SUMX(
            Regions
            ,DIVIDE(
                CALCULATE(COUNTROWS('tbl'),tbl[STATUS]="OLD")
                ,CALCULATE(COUNTROWS('tbl'))
            )
        )
RETURN 
    DIVIDE(sumOfRates,COUNTROWS(Regions))+0

enter image description here

user_190423790
Related