Measure within a Measure ignore Months/Years/Dates in a Visual

[michael_knight]

Hi All,

I’m trying to make a certain measure within a measure ignore Month/Year dates in a Line and Stacked Column chart

I am working on Line Value in this visual. I put the Total % and the % from Janaury. The chart visual in Janaury 2020, is using the 43% value when I want it to use the total value which is 34%. 

Target & Cancelled. % = [C.]/[C, DS, OA, OS, DP]* [App Tar - RHB Count]

"[C.]/[C, DS, OA, OS, DP]" is a percentage value and I want that to use the total % rather than the % for January 2020, December 2019 etc 

I hope that makes sense.

Please ask me any questions

Thanks,

Mike

[michael_knight]

Still stuck on this issue. Ay help welcomed

Thanks,

Michael

[v-juanli-msft]

Hi @michael_knight 

I reproduce your problem as below:

% for 2020 Jan,

total %

Create a measure

Measure =
SUM ( Sheet7[value] )
    - (
        AVERAGEX (
            ALLSELECTED ( 'date'[Date] ),
            [a%]
        )
            * SUM ( Sheet7[value] )
            + AVERAGEX (
                ALLSELECTED ( 'date'[Date] ),
                [c%]
            )
                * SUM ( Sheet7[value] )
    )

Add date[date] and the measure to the line chart.

7*53.33%+7*40%=6.5331

7-6.5331=0.4669~=0.47

(My date table has a relationship with sheet7 table)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michael_knight]

Hi,

Thanks for giving it a go. It means a lot. 

I don't want a total % for the month, I want a grand total %. And then use that grand total % of the full date table in the formula. 

I want the grand total over the whole date table but I also want it to use a slicer so I can select an Owner and use Shaun's grand total % from the Start to the End of the date table

Does that make sense?

I'll add the PBIX file that I'm playing around with incase you want to see what I'm playing with on my end. 

https://www.dropbox.com/s/mkmuvmupg0yk46o/Acquisitions%20-%20Help.pbix?dl=0

Cheers,

Mike

[v-juanli-msft]

Hi @michael_knight 

Create a measure

Measure =
VAR total_percent_c =
    CALCULATE (
        [C. %],
        ALLSELECTED ( 'Full Date' )
    )
VAR total_percent_ds =
    CALCULATE (
        [DS. %],
        ALLSELECTED ( 'Full Date' )
    )
RETURN
    [App Tar - RHB Count] - ( total_percent_c * [App Tar - RHB Count] + total_percent_ds * [App Tar - RHB Count] )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michael_knight]

Hi @v-juanli-msft 

Thanks for that, much appriciated. Getting closer, however I've noticed an issue with that measure. Whenever I use a slicer of some sort, whether it's Owner or Full Date, the Measure you created changes. Whenever I use a slicer it should just breakdown a value you have rather than change the value completely. 

Do you have any idea why this is happening?

Kind regards,

Mike

[v-juanli-msft]

Hi @michael_knight 

Could you clear me?

For example, which result do you want finally?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michael_knight]

Hi @v-juanli-msft 

Thanks for the reply. I'll try and explain it

I am using values from the left visual to get the total on the right visual. I'll be using Janauary as the example 

In order to get the 19 Value in the month of January the following forumla is currently being used

60 x 43% = 26

60 x 26% = 15 

15+25.8 = 41

60 - 41 = 19 

However, I want it to use the Total % for both C and DS, which would be the following

60 x 34% = 20

60 x 21% = 13

20 + 13 = 33

60 - 33 = 27 

I want the % to use the total rather than work on a month to month basis 

Does that make sense? If not, please let me know. 

Thanks,

Mike

[v-juanli-msft]

Hi @michael_knight 

Could you show me how the [c], [ds] calculate?

in my example, they are columns, then i could create measures

total c% = CALCULATE(AVERAGE('Table'[c]),ALL('Table'))

total ds% = CALCULATE(AVERAGE('Table'[ds]),ALL('Table'))

easure = SUM([value])-(SUM('Table'[value])*[total c%]+SUM('Table'[value])*[total ds%])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michael_knight]

Hi @v-juanli-msft ,

Thanks for the reply. There's quite the change of measure, I'll put them both below 

Cancelled (WBAH) - Count = COUNTROWS(FILTER(Opportunity, Opportunity[statuscode_display] = "Cancelled" && Opportunity[Company] = "Company"))

Declined by Sales (WBAH) - Count = COUNTROWS(FILTER(Opportunity, Opportunity[statuscode_display] = "Declined by Sales" && Opportunity[Company] = "Company"))

Declined by Field Agent (WBAH) - Count = COUNTROWS(FILTER(Opportunity, Opportunity[statuscode_display] = "Declined by Field Manager" && Opportunity[Company] = "Company"))

Fall Through (WBAH) - Count = COUNTROWS(FILTER(Opportunity, Opportunity[statuscode_display] = "Fall Through" && Opportunity[Company] = "Company"))
____________________________

Cancelled - WBAH = 
CALCULATE([Cancelled (WBAH) - Count],
USERELATIONSHIP('Opportunity'[Viewing Start Date], 'Full Date'[Full Date]))

Declined by Sales - WBAH = 
CALCULATE([Declined by Sales (WBAH) - Count],
USERELATIONSHIP('Opportunity'[Viewing Start Date], 'Full Date'[Full Date]))

Declined by Field Agent - WBAH = 
CALCULATE([Declined by Field Agent (WBAH) - Count],
USERELATIONSHIP('Opportunity'[Viewing Start Date], 'Full Date'[Full Date]))

Fall Through - WBAH = 
CALCULATE([Fall Through (WBAH) - Count],
USERELATIONSHIP('Opportunity'[Viewing Start Date], 'Full Date'[Full Date]))
____________________________

C = [Cancelled - WBAH] + [Declined by Sales - WBAH] + [Declined by Field Agent - WBAH] + [Fall Through - WBAH]
____________________________

C % = DIVIDE([C], [Didn't Sign & Cancellations + OS, OA, DP (WBAH)])

I put them in chronological order. DS uses the same methodology as C 

Any other question (I know it's fairly confusing) please ask me 

Kinda regards,

Mike