Search code examples
powerbidax

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

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

Intuitively 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 so straightforward yet for me, since I am learning. 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 how to do that in DAX in Power PI. I would be interested to know if there is some code online to do this, or if there is a special built-in function to calculate this horizontal line.

Solution

  • 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://learn.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane

    FFFd