Total %

[unnijoy]

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 quarter. 
The sales % for each item in a QTR shoud be calculated by toatal sale of each manager under each category  divided by overall sales for that particular QTR. 
Required Out put

Name	Laptop	Server	Laptop	Server	Laptop	Server	Laptop	Server	Laptop	Server
Q1	Q1	Q2	Q2	Q3	Q3	Q4	Q4	Q5	Q5
Alex	2%	3%	9%	4%	11%	4%	7%	13%	15%	15%
James	5%	4%	11%	6%	12%	7%	8%	11%	7%	13%
Rex	9%	16%	13%	10%	14%	9%	10%	9%	14%	10%
Glen	13%	15%	16%	10%	16%	4%	11%	7%	13%	7%
Tom	17%	16%	18%	3%	18%	6%	13%	12%	3%	3%

Current Table

Name	Laptop	Server	Laptop	Server	Laptop	Server	Laptop	Server	Laptop	Server
Q1	Q1	Q2	Q2	Q3	Q3	Q4	Q4	Q5	Q5
Alex	22	45	78	32	65	23	54	98	98	98
James	78	54	98	54	76	43	65	87	46	87
Rex	134	233	118	89	87	53	76	67	90	67
Glen	190	211	138	90	98	26	87	56	87	45
Tom	246	234	158	23	109	34	98	90	23	23

Total

1447

878

614

778

664

Current DAX:

Sales Last 5 Quarters (Axis) =

VAR AnchorDate =

    COALESCE (

        SELECTEDVALUE ( 'Date'[QuarterStartDate] ),     -- from global slicer

        MAX ( SalesFact[QuarterStartDate] )             -- fallback

    )

VAR AxisQ = SELECTEDVALUE ( 'Quarter Axis'[QuarterStartDate] )

RETURN

CALCULATE (

    [Sales Amount],

    REMOVEFILTERS ( 'Date' ),                       

    KEEPFILTERS ( DATESINPERIOD ( 'Date'[Date], AnchorDate, -5, QUARTER ) ),

    TREATAS ( { AxisQ }, 'Date'[QuarterStartDate] )     

)

Dax for Visual level filter = 

Axis Flag Last 5 =

VAR AnchorDate =

    COALESCE (

        SELECTEDVALUE ( 'Date'[QuarterStartDate] ),

        MAX ( SalesFact[QuarterStartDate] )

    )

VAR AxisQ = SELECTEDVALUE ( 'Quarter Axis'[QuarterStartDate] )

VAR InWindow =

    CALCULATE (

        COUNTROWS ( DATESINPERIOD ( 'Date'[Date], AnchorDate, -5, QUARTER ) ),

        REMOVEFILTERS ( 'Date' ),

        TREATAS ( { AxisQ }, 'Date'[QuarterStartDate] )

    )

RETURN IF ( InWindow > 0, 1, 0 )

How can i alter the dax to achive the result that i need.  ( Required Output )

[v-venuppu]

Hi @unnijoy ,

Sorry for the Inconvenience...!!
I have shared the possible ways to share the file and if we dont have sufficient data, we cannot provide sufficient resolution.

Thank you.

[v-venuppu]

Hi @unnijoy ,

Can you please share the PBIX file as requested, so that it will be helpful for us to solve the issue.

Thank you.

[unnijoy]

I am using my Company One drive. I can't share that . Do we have any other option.

[v-venuppu]

Hi @unnijoy ,

Can you please share the requested details to solve the issue.

Thank you.

[v-venuppu]

Hi @unnijoy ,

Can you please upload the file into Onedrive and share the link of Onedrive here, so that will download and work on it.

Thank you.

[v-venuppu]

Hi @unnijoy ,

Thank you for sharing the details.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

For attaching PBIX file please find the below screenshot for your reference:
While replying to the post, you will get this option below, where you can browse and attach your file.

[unnijoy]

@v-venuppu  Thanks for your reply. I am not getting the option to attach the file. The below screen shote shows what i am getting. Am i Missing somthing.

[v-venuppu]

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]

@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.

[v-venuppu]

Hi @unnijoy ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Shahid12523 for the prompt response.

As Manager and Item are coming from different dimension tables, we need to ensure that the "Total Sales per Quarter" measure removes filters from both tables.

Try this adjusted measure:

Total Sales per Quarter =
VAR AnchorDate =
COALESCE (
SELECTEDVALUE ( 'Date'[QuarterStartDate] ),
MAX ( SalesFact[QuarterStartDate] )
)
VAR AxisQ = SELECTEDVALUE ( 'Quarter Axis'[QuarterStartDate] )
RETURN
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( 'Date', 'Manager', 'Category' ),
KEEPFILTERS ( DATESINPERIOD ( 'Date'[Date], AnchorDate, -5, QUARTER ) ),
TREATAS ( { AxisQ }, 'Date'[QuarterStartDate] )
)
Then define % measure (You can use the same % measure given by @Shahid12523 in his post.

Use this % measure in your matrix. It will show the correct per-item, per-manager percentage split within each quarter.
If this doesn't help, kindly share the PBIX file, so that it will be helpful for us to solve the issue.

[unnijoy]

@v-venuppu  thanks for your reply. i tried but am getting 100 percent for all

[Shahid12523]

Keep your [Sales Last 5 Quarters (Axis)] measure.

Create quarter total:

Total Sales per Quarter =
CALCULATE (
[Sales Last 5 Quarters (Axis)],
REMOVEFILTERS ( 'Manager'[Name], 'Category'[Category] )
)

% measure:

Sales % of Quarter =
DIVIDE (
[Sales Last 5 Quarters (Axis)],
[Total Sales per Quarter]
)

Use Sales % of Quarter in your matrix → it will give the percentages exactly like your required output.

[unnijoy]

@Shahid12523 , for me manager name and Items are coming from two different table. And when i use the dax that you share. i am getting every thing as 100%.

https://community.fabric.microsoft.com/t5/Desktop/Last-3-quarter/m-p/4806171#M1429711

the current solution of getting the numbers is from the above link. i think we may have to alter the "Total Sales per Quarter " Dax as the manger name and item names are coming from two different tables.