Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
11 months ago

Total %

I have a set up in which if i select a Qtr in the filter the table will show the last 5 QTR sales including the selected quarter. Now i need to show the Sales Total Percentage for each item in each q...
Need Help
v-venuppu's avatar
v-venuppu
Community Support
11 months ago

Hi unnijoy ,

Can you please share the PBIX file along with data model, so that it will be helpful for us to solve the issue.

Thank you.

  • unnijoy's avatar
    unnijoy
    Post Prodigy
    11 months ago

    v-venuppu thanks for your reply. I am not finding any option to load the file in this. I am attaching the screenshote of the model

    In the table we got the High,Medium and low risks from the table (Table - PPRI Table). And in the column we have QTR (Table -  QtrSlice) and Business critical ( Table - Business Table). Value is based on the Dax (Last4_Qtr_Business_Critical). and for getting the last 4 QTR we are using a dax to filter (Last4Filter).
    As you can see from the table that we are getting the Number of employees at each risk. Our final goal is to get the %. 

    So lets take Q2 2024. We can see Q2 2024 got High ,Medium  and low in ceach column. The denominator should be the sum of Q2 2024 overall total. in this case it should be 797+1206+1514 = 3517.
    So the % for Q2 2024 will be High Risk = 258/3517=7.3%,  Medium Risk = 219/3517=6.2%, Low Risk=320/3517=9.1%.
    The tables are below.
    Business Table

    PPRI Table

    Calander

    QtrSlice

    DAX used

    Last4_Qtr_Business_Critical =
    VAR AnchorDate =
        COALESCE (
            SELECTEDVALUE ( 'Calander'[Qtr Start date]),    
            MAX ( Sheet1[Qtr Start date] )        
        )
    VAR AxisQ = SELECTEDVALUE ( QtrSlice[Qtr Start Date] )
    RETURN
    CALCULATE (
        [Count_WDID],
        REMOVEFILTERS ( Calander ),                        
        KEEPFILTERS ( DATESINPERIOD ( Calander[Date], AnchorDate, -4, QUARTER ) ),
        TREATAS ( { AxisQ }, Calander[Qtr Start date] )    
    )

    ---------------

    Last4Filter =
    VAR AnchorDate =
        COALESCE (
            SELECTEDVALUE ( Calander[Qtr Start date] ),
            MAX ( Sheet1[Qtr Start date] )
        )
    VAR AxisQ = SELECTEDVALUE ( QtrSlice[Qtr Start Date] )
    VAR InWindow =
        CALCULATE (
            COUNTROWS ( DATESINPERIOD ( Calander[Date], AnchorDate, -4, QUARTER ) ),
            REMOVEFILTERS ( Calander ),
            TREATAS ( { AxisQ }, Calander[Qtr Start date] )
        )
    RETURN IF ( InWindow > 0, 1, 0 )
    ---------------------------
    Year and Qtr slicer is coming from Calander table.

    Hope this will help you...
    Thank you for your quick response and help.