Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Complex Dax Help :) Please Help

    Bronze Contributor
    Posted Dec 09, 2021 01:36 PM
      |   view attached
    I need one measure that can help with the below examples



    ------------------------------
    Beau A
    Analyst
    MI
    ------------------------------

    Attachment(s)



  • 2.  RE: Complex Dax Help :) Please Help

    Posted Dec 13, 2021 09:54 PM
    This measure should do what you want. The trick here is that DISTINCTCOUNT( Dates[Date_Month] ) is calculated for different contexts in the two instances that it is used: the first time with a global context, the second time with a Dim_Site[Id] row context.

    Average of Sites with All Months :=
    VAR __selectedMonths =
        DISTINCTCOUNT( Dates[Date_Month] )
    VAR __sitesWithAllMonths =
        FILTER(
            SUMMARIZE(
                F_Consol_PL
                , Dim_Site[Id]
                , "Months With Data"DISTINCTCOUNT( Dates[Date_Month] )
                , "Subtotal"SUM( F_Consol_PL[Value] )
            )
            , [Months With Data] = __selectedMonths
        )
    RETURN
        DIVIDE(
            SUMX( __sitesWithAllMonths, [Subtotal] )
            , COUNTROWS( __sitesWithAllMonths )
            , BLANK()
        )

    ------------------------------
    Lan Huynh
    Data Visualisation Developer
    ------------------------------



  • 3.  RE: Complex Dax Help :) Please Help

    Bronze Contributor
    Posted Dec 14, 2021 09:30 AM
    Hi Lan - I downloaded my file and then added your measure, but only blank appears. Are you able to send my file back with your measure tested? That would be a huge help.



    ------------------------------
    Beau A
    Analyst
    MI
    ------------------------------



  • 4.  RE: Complex Dax Help :) Please Help

    Top Contributor
    Posted Dec 15, 2021 02:36 AM

    Hi @Beau A,

    Please can you try the following 2 codes​, one is the total of values and the other is the average.

    Total Values = SUM(F_Consol_PL[Value])
    
    New AVG = 
    var Selected_Site = SELECTEDVALUE(Dim_Site[Name])
    var Selected_State = SELECTEDVALUE(Dim_Site[State])
    var New_AVG = AVERAGEX(Filter(ALL(Dim_Site), Dim_Site[State] = Selected_State), [Total Values])
    //var visisble_values = CONCATENATEX(Selected_State, VALUES(Dim_Site[Name]),",")
    Return
    IF(hasonevalue(Dim_Site[State]),New_AVG, AVERAGEX(Dim_Site, [Total Values]))



    This is running on the assumption that the Site names are unique.
    At the end of the variable, there is a provision to give a simple average over the site table, in case there are no values selected on the filters.

    Hope this helps and please mark is as the best answer if it solves your problem.

    Thank you,



    ------------------------------
    Vishesh Jain
    MCSA Power BI and Excel
    Emerald Award Dynamic Communities 2019
    Owner
    VR Construction
    ------------------------------



  • 5.  RE: Complex Dax Help :) Please Help

    Bronze Contributor
    Posted Dec 15, 2021 01:37 PM
      |   view attached
    Hey Vishesh - it looks like your code works perfect.. besides one situation.

    Even if a site is from the same state, it should not be included (at all) if it is missing any of the selected filter dates.
    - in the below example the correct (New Avg = 4) since site 2 is missing (2021-01). For that reason Site2 should be excluded.

    I'm including the file below if you can quickly adjust.. that would be AMAZING!!!


    ------------------------------
    Beau A
    Analyst
    MI
    ------------------------------

    Attachment(s)



  • 6.  RE: Complex Dax Help :) Please Help

    Posted Dec 16, 2021 01:49 AM
      |   view attached
    @Beau A: I suspect it was blank for you because you didn't have a Dates[Date_Month] slicer/filter with only the relevant 4 months selected. No site has data for all 12 months-which is the default filter context for that column-so no sites are included in the measure. See attached.

    ------------------------------
    Lan Huynh
    Data Visualisation Developer
    ------------------------------

    Attachment(s)



  • 7.  RE: Complex Dax Help :) Please Help

    Top Contributor
    Posted Dec 28, 2021 03:23 AM

    Hi @Beau A

    Here is the code for your 2nd requirement, however for I might have forgotten the first requirement and I can't remember it.
    Maybe you can figure out someway using the SWITCH() and TRUE() functions to get the output using both the codes.

    New AVG 2 =
    VAR Selected_Site =
        SELECTEDVALUE ( Dim_Site[Name] )
    VAR Selected_State =
        SELECTEDVALUE ( Dim_Site[State] ) //var New_AVG = AVERAGEX(Filter(ALL(Dim_Site), Dim_Site[State] = Selected_State), [Total Values])
    VAR req_table =
        FILTER (
            ADDCOLUMNS (
                CROSSJOIN (
                    SELECTCOLUMNS ( Dim_Site, "@Site_ID", Dim_Site[ID] ),
                    SELECTCOLUMNS ( VALUES ( Dates[Date_Month] ), "@Year_Month", Dates[Date_Month] )
                ),
                "@Values", [Total Values]
            ),
            [@Values] = BLANK ()
        )
    VAR individual_site =
        DISTINCT ( SELECTCOLUMNS ( req_table, "@site_id_new", [@Site_ID] ) )
    VAR sites =
        CONCATENATEX ( individual_site, [@site_id_new], ",", [@site_id_new] )
    VAR New_AVG =
        AVERAGEX (
            FILTER (
                ALL ( Dim_Site ),
                Dim_Site[State] = Selected_State
                    && EXCEPT ( VALUES ( Dim_Site[Id] ), individual_site )
            ),
            [Total Values]
        )
    VAR row_count_needed =
        CALCULATE (
            COUNTROWS ( F_Consol_PL ),
            EXCEPT ( VALUES ( Dim_Site[Id] ), individual_site )
        )
    VAR sum_needed =
        CALCULATE (
            [Total Values],
            EXCEPT ( VALUES ( Dim_Site[Id] ), individual_site )
        )
    VAR new_avg2 =
        DIVIDE ( sum_needed, row_count_needed )
    RETURN
        new_avg2
    //IF(hasonevalue(Dim_Site[State]),AVERAGEX(Dim_Site, [Total Values]), new_avg2)​
    ​​​​Sorry for providing you a work in progress code.

    Thank you,

    ------------------------------
    Vishesh Jain
    MCSA Power BI and Excel
    Emerald Award Dynamic Communities 2019
    Owner
    VR Construction
    ------------------------------