Need a Measure to show exact values in the table to matrix visual

[BharathM]

 Hi Everyone

The above is the requirement for me to show the data as per the location irrespective of real totals we have to use the total in the measure total column as posted in the left side table image,

These values should only change if there is change in the date such as month or year but for daily it should show the same value .

I really need your help in this.

[v-jianboli-msft]

Hi @BharathM ,

Maybe Tooltips can help you? You can refer to threads like this one.
Sorry this question is beyond the topic at the beginning of the thread, in order to be able to make the thread more relevant and better help other users with similar questions, please consider Marking the replies that help you and creating a new thread for the new question, this will also allow more users to participate and help you.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @BharathM ,

Please try:

Add a new column MonthNumber to the table:

MonthNumber = 
var _a = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2022,12,31)),"Month",FORMAT([Date],"MMMM"))
return MONTH(MAXX(FILTER(_a,[Month]=[Months]),[Date]))

Output:

Apply the measure:

Measure2 =
IF (
    ISINSCOPE ( 'Source Table (2)'[Attribute] ),
    CALCULATE (
        SUM ( 'Source Table (2)'[Value] ),
        FILTER (
            'Source Table (2)',
            [Year] = MAX ( 'Source Table (2)'[Year] )
                && [MonthNumber] = MAX ( 'Source Table (2)'[MonthNumber] )
        )
    ),
    SUMX (
        SUMMARIZE (
            FILTER (
                'Source Table (2)',
                [Year] = MAX ( 'Source Table (2)'[Year] )
                    && [MonthNumber] = MAX ( 'Source Table (2)'[MonthNumber] )
            ),
            'Source Table (2)'[Total]
        ),
        [Total]
    )
)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BharathM]

Hi @v-jianboli-msft @Greg_Deckler 

thank you for your effort but im trying relate it with another table and using that tables month and year filters can you make it possible so that filtering with other tables month and year slicers,
and also locations is also in other table such as DLF data, year also in it and month also in it .

we are using in-active relationship between both the DeliveryCostTarget and All Data, in all data we are taking slicers such as month,year and columns in the visual are Locations from DLF data.

The raw data that i have provided earlier are from Deliverycosttarget.
Can you Please help me in this I know you have provided your excellent help in this but values are not filtering according to selection they are summing up

the above is the screen shot for the values when selected and summing up on sleecting but totals are differentiated.

[v-jianboli-msft]

Hi @BharathM ,

Sorry for my late reply. According to my knowledge, this issue may caused by the relationships between your tables. I'm not sure what the DLF Table is used for, but if the year and month you select is from the ALL DATA table, it will not filter the DeliveryCostTarget table correctly. Because the relationship between DLF Table and ALL DATA is a single one-to-many relationship. Please consider removing the relationship between DLF Table and ALL DATA and activate the relationship between DeliveryCostTarget and ALL DATA.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BharathM]

Hi @v-jianboli-msft 

Here is the pbix file Need a Measure to show exact values in the table to matrix visual (1).pbix

In this table (DLF) is used because of a requirement to show every location for every fueltype combination and date combination. so that table columns wont reduce and will remain constant on any selection,

I am just passing you limited data,
but entire slicers in the current model are from ALL DATA only

we are not using any slicers from different table.


My measure should work along with different views like MONTHLY ,WEEKLY ,DAILY ,YEARLY
but main issue is it does not filter as per date selection or any year selection etc.

requirement is to show the fixed values of entire month of that year in a selected daily weekly and monthly slicer. it should not filter with the FUEL TYPE slicer as there is no use of that in the FIXED VALUES

can you please help me in this its an urgent task and I am really glad that You are responding to my every query , you are my last Hope in this .

Thank you soo much.

[v-jianboli-msft]

Hi @BharathM ,

Please try:

Measure 3 = 
var _a = CALCULATE(MAX('All Data'[YEAR]),REMOVEFILTERS('DLF TABLE'[Location]))*100+MONTH(CALCULATE(MAX('All Data'[Shiftdate]),REMOVEFILTERS('DLF TABLE'[Location])))
var _b = CALCULATE(MAX('DLF TABLE'[Location]),ALL('All Data'))
return 
IF (
    ISINSCOPE('DLF TABLE'[Location]),
SUMX(FILTER(ALL('Source Table (2)'),[Year]*100+[MonthNumber]=_a&&[Attribute]=_b),[Value]),
    SUMX (
        SUMMARIZE (
            FILTER (
                ALL('Source Table (2)'),
                [Year]*100+[MonthNumber]=_a
            ),
            'Source Table (2)'[Total]
        ),
        [Total]
    )
)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BharathM]

hi @v-jianboli-msft 

Thanks a Tonne times for your solution its working fine only thing I have changed is the variable B  = Values(DLFtable[location])
it worked fine but now onlything left is when we seelect year it should show the "Total " in the months Column for that Specific Year,

Currently It shows Latest Months data in the year Selected

the following requiremnt is only for year page ,Can you Please solve this one as well it would be superb wonderful , Thank you soo much for your help 😊😊

[v-jianboli-msft · Accepted solution]

Hi @BharathM ,

Please try:

Measure3 =
VAR _b =
    CALCULATE ( MAX ( 'DLF TABLE'[Location] ), ALL ( 'All Data' ) )
VAR _c =
    CALCULATE ( MAX ( 'All Data'[YEAR] ), REMOVEFILTERS ( 'DLF TABLE'[Location] ) )
VAR _a =
    _c * 100
        + MONTH (
            CALCULATE (
                MAX ( 'All Data'[Shiftdate] ),
                REMOVEFILTERS ( 'DLF TABLE'[Location] )
            )
        )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'All Data'[YEAR] ) && ISINSCOPE ( 'DLF TABLE'[Location] ),
            SUMX (
                FILTER (
                    ALL ( 'Source Table (2)' ),
                    'Source Table (2)'[Months] = "Total"
                        && [Attribute] = _b
                        && [Year] = _c
                ),
                [Value]
            ),
        ISFILTERED ( 'All Data'[YEAR] ) && NOT ( ISINSCOPE ( 'DLF TABLE'[Location] ) ),
            SUMX (
                SUMMARIZE (
                    FILTER ( ALL ( 'Source Table (2)' ), [Year] = _c && [Months] = "Total" ),
                    'Source Table (2)'[Total]
                ),
                [Total]
            ),
        NOT ( ISFILTERED ( 'All Data'[YEAR] ) ) && ISINSCOPE ( 'DLF TABLE'[Location] ),
            SUMX (
                FILTER (
                    ALL ( 'Source Table (2)' ),
                    [Year] * 100 + [MonthNumber] = _a
                        && [Attribute] = _b
                ),
                [Value]
            ),
        SUMX (
            SUMMARIZE (
                FILTER ( ALL ( 'Source Table (2)' ), [Year] * 100 + [MonthNumber] = _a ),
                'Source Table (2)'[Total]
            ),
            [Total]
        )
    )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BharathM]

Hi @v-jianboli-msft  

Thank You So Much ,Thank You x1000 times its worked 

There is a last Requirement on this Matrix Visual when we Hover on Measure 3 name in the visual we need to show the Hover Over Descriptions on Each Title 

For Example Hover on Measure 3 name shoud display "The Deliveries are Completed" 

Like this i have 20 plus measures in the matrix visual for each there is a seperate Descriptions   Pleae Give one Example How to do this


Thanks So much

[v-jianboli-msft]

Hi @BharathM ,

Sorry, there is some complexity in what you are describing and I am not sure I understand it correctly.
According to your description do you need the visual to contain only one row? If it is only one line this line should show the values according to the year and month you have chosen and the total value is the total provided by the raw data and not the real total, right? Or are there other rows in this visual, and if so, what are they? Also you mentioned something about dates, but I don't see where the dates are in the data you provided.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @BharathM ,

Please try:

First unpivot the columns:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVbNbpw3DHyXPQeCJIr6OQYIcgjQ9NDeDB8cY9EEcOJgawfo25fkjOzddU7CtyuJ5HBmqJubw6e7H893p/8O7w411+qLpBnrStnXImk1X2sq8d1Tn/5/S1r8e6bhv4skHf77SGPgXC24ry3/XqmUw+27m8PH45fTVcySsseQlkrs1STd14Y7yvCztk/THLgrYktFrnZOFblVwb4aOeY0VsT84+50//Us4EpNuTIQLtC0JhIqA6sKigwQbP9ArUuQR1fE81tqdQg83Pufp28Pr+Esv7FwrrAemQhXiSXSaknYgtheCGEhHBPw2LZREG/t8s7Q9GOTyDcWtVHcHeJ12b+bZRNoLvy/s7J1NOy7QvPT84/jazy17gm7ETD0lCO9ifQtn9zZHUdLCb5YOcBU4nhmVQsEs2+pDPdwVl7LaXVcG8SytNY5Ie1cL2hO83K6EXOhzEay9TgnyHESk9rRuud/nv99OqtOkbUo9NAU1Ri6AxdABj15ElPj1zYJgYllMFdCUfmtEMNfx59Px+9fjqfXiL1BaUIyesQI1SlLRRUBovakHagA1PwSIaq0pgSznXrg5p/3T48X8ax2ADmpMkl5AkBUasAC50r2BQ5WQiMZG8lIDsM/Xsj5+fHXVYUySHCrUJHBJPErhTSERVIAvWN/MNGK2ql0Uidk7M1AzA/H+6uYvpdanKRJ0KLSa7yfFWynOAJX4BbHkFpBudJTaQiZQZy/H5/uzjVfEzvOisp2wYFoQCvTTTs0atcGeQ2R3ebQlvMTRSx08cK3BRE6d+o4V72mIiBoE0SM1dxgTCQYvbAM4Ekdv9t9smUkv/Ft+t9iP9rEneEAhf10UTRUoZOgCmIWnpuUZFGUl9nvWq99m8VB68PJBSMljLItSlkcLW+R2rspe0AA1sbz4gPoyrkF7Y8+COVmASa9bLOGUll05AGR9gvJCHEQWJKtpV4at+DYoHRCwz5aOWKFMgkr8rEYxtAwlUxOGJMmVKIB4x5MMrvMLo3b45lLNVIA6GSisSBLoafY6A+pmNdgf+ccYrttcFRqIX73e8aVcweYkyitV6tQnKPhk7Cxu2eI3+2PYHaenpRIIye6vLFu/0crjM2aGHhq5kmLN6ldPnGCDbNgQljEzueGeF2NkvH+UNPrt+7NtBd9Jjje1ovfFAYPDhROXRUaymSQCQobqJNgLr6v2lv/Fig35oWtfLMsvlkKNcV3G95nFZIQPn2MizSaQWoqHw/0tUv7jj4qr2iUbcdDo8Ly3DkqKlQ2Lq70F+Ie0AxF1uBt1R2wt+bNwSDsZKbA9mjKFBieLg2glcHBUKCIPQWdQx2Zy45Zr92bE4UKyGTqhU8J/a5zTimw9XfewPfAqbWfre2F77e3/wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Months = _t, Year = _t, Akron = _t, Albany = _t, CherryCreek = _t, Crabb = _t, Kirkwood = _t, Bath = _t, Cato = _t, Romulus = _t, Syracuse = _t, Thrifty = _t, Tonawanda = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Months", type text}, {"Year", Int64.Type}, {"Akron", type number}, {"Albany", type number}, {"CherryCreek", type number}, {"Crabb", type number}, {"Kirkwood", type number}, {"Bath", type number}, {"Cato", type number}, {"Romulus", type number}, {"Syracuse", type number}, {"Thrifty", type number}, {"Tonawanda", type number}, {"Total", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Months", "Year", "Total"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Then apply the measure to the matrix visual:

Measure =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Source Table (2)'[Months] )
        && ISINSCOPE ( 'Source Table (2)'[Attribute] ), SUM ( 'Source Table (2)'[Value] ),
    ISINSCOPE ( 'Source Table (2)'[Months] )
        && NOT ( ISINSCOPE ( 'Source Table (2)'[Attribute] ) ), SUMX ( SUMMARIZE ( 'Source Table (2)', 'Source Table (2)'[Total] ), [Total] ),
    NOT ( ISINSCOPE ( 'Source Table (2)'[Months] ) )
        && ISINSCOPE ( 'Source Table (2)'[Attribute] ),
        CALCULATE (
            SUM ( 'Source Table (2)'[Value] ),
            FILTER ( 'Source Table (2)', [Months] = "Total" )
        ),
    SUMX (
        SUMMARIZE (
            FILTER ( 'Source Table (2)', [Months] = "Total" ),
            'Source Table (2)'[Total]
        ),
        [Total]
    )
)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BharathM]

Hi @v-jianboli-msft 

And also below the September month Total row is there right 

This total row should shown only on year 2022 selected in the slicer Not display the jan feb etc this months 

When selected 2023 respect total row should display not the months names values

[BharathM]

Hi @v-jianboli-msft 
Thanks for the soulution it works nearly as expected ,but only hurdle now is we are not going to place year or month coulmn in the matrics visual just the measure name( we have enabled the Switch values to Rows option)

the year and Month columns are selectd in slicer and the value of the measure should cange accordingly.

But we dont have the option to place month or year in the Matrix visual it shows first year or first month.

There is also one more additional requirement such as if we select a single date then the data should only show the Month to which that date is related as we currently have monthly analysis it should also work on daily analysis.



For suppose for JAN 2022 we have the data in the table as per our Prevous post, i want that data to be repeated on every selection of single date in the month of JAN 2022.
If its possible please help me out its really appreciated

Can you please help.

[Greg_Deckler]

@BharathM Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[BharathM]

Hi @Greg_Deckler Here is the pbix file

20230104locations.pbix