Forum Discussion

UDMH's avatar
UDMH
Frequent Visitor
2 years ago
Solved

Power BI Running total for Divide function

For Example,

Here I have a sales data against Product.

 





Show and Tell
  • MFelix's avatar
    MFelix
    2 years ago

    Hi UDMH ,

     

    For the number of rows add a table with the folowing data:

     

     

    Add the following measures:

    Percent Rank = RANKX ( ALLSELECTED(  Sales ), [Sales], , DESC )

Percent Running Total = 
VAR __rank = [_Percent Rank]
RETURN
SUMX (  FILTER ( ALLSELECTED ( Sales ), [Percent Rank] <= __rank ), [Sales] ) 

Nº Items = 
VAR __countBelowPercent =  [Sales]  * SELECTEDVALUE('Category'[Min])--store threshold percentabge
VAR __countBelowPercent1 =  [Sales]  * SELECTEDVALUE('Category'[MAX])
RETURN 
COUNTX ( 
    Sales, 
    IF ( [_Percent Running Total] >= __countBelowPercent  && [_Percent Running Total] <= __countBelowPercent1  , 1 ) 
)

     

     

     

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi UDMH ,

     

    Create the following measures:

    Sales = SUM(Sales[Values])

Percentage = DIVIDE(
			[Sales],
			CALCULATE(
				[Sales],
				REMOVEFILTERS(Sales[Prod])
			)
		)


Running Sum Percentage = CALCULATE(
			[Percentage],
			WINDOW(
				1,
				ABS,
				0,
				REL,
				SUMMARIZE(
					ALLSELECTED(Sales),
					Sales[Prod],
					"SalesTotal", [Sales]
				),
				ORDERBY(
					[SalesTotal],
					DESC
				)
			)
		)

     

     

     

    • UDMH's avatar
      UDMH
      Frequent Visitor
      2 years ago

      But, how this works based on year wise data

      • MFelix's avatar
        MFelix
        Super User
        2 years ago

        Hi UDMH 


        What do you mean by a Year wise data?

        In the example you gave there was only Product and Sales Amount. Can you please elaborate on the final result you need to achieve,