I am probably going about this the wrong way.
Currently I have SQL-code that works well showing a Count of 'Exit Reasons' in a database. I can use that code of the bat in a Power BI Query and get a nice Pie Chart. Pie chart automagicly creates Count of 'Exit Reasons' in Visual level filters.
Next to it I want to use a Table including the same data. Chosing the same Field in the Table however does not create a Count of 'Exit Reasons'. So I created a Measure New Measure = SUM('Query1'[Amount]). This one gives me a count per reason from my SQL-code.
In Report Builder I simply use an Expression to calculate the % of the total for each reason. =Sum(Fields!Amount.Value) / ReportItems!OutTransfersTotSum.Value
I am at loss for how to make this in Dax. What I would like is to change the code in my measure to something like. New Measure = (SUM('Query1'[Amount])/Total(Query1'[Amount])) * 100
Solved! Go to Solution.
SUM('Query1'[Amount])/CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons]))
The syntax is ok but I get 0 on every row and a Total of 1
SUM('Query1'[Amount])/CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Amount]))
1 on every row and a Total of 1.
To add insult to injury I tried to SUM('Query1'[Amount]) / 5. In the hope of getting 671/5, 1/5, 2/5, etc. That works and the grand total checks out. I will fiddle around with the SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons]))
CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons])) returns 10268 instead of the correct sum 5237 so it seems that my Filter is off. I need to remove Blank and a rouge row.
ALLSELECTED as you wrote does the trick. So the final touch is to * 100 to get % instead of decimals. The only bit left is that I want to show 2 decimals instead of being rounded down to 0. The total is now 100.
Measure = = CALCULATE(SUM('Query1'[Amount])/CALCULATE(SUM ('Query1'[Amount]);ALLSELECTED('Query1'[Exit Reason]))*100)
13, 0, 0, 0, 12 .. Total 100
I removed * 100 and Format (). Then I could change to Modeling / Format Percentage and 2 digits.
@Jackofall try doing the next formula, SUM('Query1'[Amount])/CALCULATED(SUM('Query1'[Amount]);ALLSELECTED('Query1'[Exit Reasons]))
SUM('Query1'[Amount])/CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons]))
The syntax is ok but I get 0 on every row and a Total of 1
SUM('Query1'[Amount])/CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Amount]))
1 on every row and a Total of 1.
To add insult to injury I tried to SUM('Query1'[Amount]) / 5. In the hope of getting 671/5, 1/5, 2/5, etc. That works and the grand total checks out. I will fiddle around with the SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons]))
CALCULATE(SUM('Query1'[Amount]);ALL('Query1'[Exit Reasons])) returns 10268 instead of the correct sum 5237 so it seems that my Filter is off. I need to remove Blank and a rouge row.
ALLSELECTED as you wrote does the trick. So the final touch is to * 100 to get % instead of decimals. The only bit left is that I want to show 2 decimals instead of being rounded down to 0. The total is now 100.
Measure = = CALCULATE(SUM('Query1'[Amount])/CALCULATE(SUM ('Query1'[Amount]);ALLSELECTED('Query1'[Exit Reason]))*100)
13, 0, 0, 0, 12 .. Total 100
I removed * 100 and Format (). Then I could change to Modeling / Format Percentage and 2 digits.
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
User | Count |
---|---|
252 | |
64 | |
48 | |
46 | |
40 |
User | Count |
---|---|
348 | |
77 | |
70 | |
63 | |
60 |